Duplicate lines with postgres window functions

Question

The postgres documentation (http://www.postgresql.org/docs/9.1/static/tutorial-window.html) talks about window functions.

In their example:

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

duplicates are handled as follows:

 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700 <-- notice duplicate rows have the same value
   4800 | 25700 <-- SAME AS ABOVE
   5000 | 30700
   5200 | 41100 <-- same as next line
   5200 | 41100 <--
   6000 | 47100
(10 rows)

How do you do the same thing making it so that duplicate rows are NOT given the same value? In other words, I would like this table to look as follows:

 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 20900 <-- not the same as the next line
   4800 | 25700 <-- 
   5000 | 30700
   5200 | 35900 <-- not the same as the next line
   5200 | 41100 <--
   6000 | 47100
(10 rows)

Clodoaldo Neto · Accepted Answer · 2014-08-01 19:01:59Z

9

Use rows in the frame clause in instead of the default range

select
    salary,
    sum(salary) over (
        order by salary
        rows unbounded preceding
    )
from empsalary
;
 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 20900
   4800 | 25700
   5000 | 30700
   5200 | 35900
   5200 | 41100
   6000 | 47100

http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS

answered Aug 1, 2014 at 19:01

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Duplicate lines with postgres window functions

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related