10

I'm using an aggregate function with the OVER clause in PostgreSQL 9.1 and I want to return just the last row for each window. The last_value() window function sounds like it might do what I want - but it doesn't. It returns a row for each row in the window, whereas I want just one row per window

A simplified example:

SELECT a, some_func_like_last_value(b) OVER (PARTITION BY a ORDER BY b)
FROM
(
    SELECT 1 AS a, 'do not want this' AS b
    UNION SELECT 1, 'just want this'
) sub

I want this to return one row:

1, 'just want this'
EMP
  • 59,148
  • 53
  • 164
  • 220

2 Answers2

17

DISTINCT plus window function

Add a DISTINCT clause:

SELECT DISTINCT a
     , last_value(b) OVER (PARTITION BY a ORDER BY b
                           RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM  (
   VALUES
     (1, 'do not want this')
    ,(1, 'just want this')
   ) sub(a, b);

More about DISTINCT:

Simpler and faster with DISTINCT ON

PostgreSQL also has this extension of the SQL standard:

SELECT DISTINCT ON (a)
       a, b
FROM  (
   VALUES
     (1, 'do not want this')
   , (1, 'just want this')
   ) sub(a, b)
ORDER  BY a, b DESC;

More about DISTINCT ON and possibly faster alternatives:

Simple case with plain aggregate

If your case is actually as simple as your demo (and you don't need additional columns from that last row), a plain aggregate function will be simpler:

SELECT a, max(b)
FROM  (
   VALUES
     (1, 'do not want this')
   , (1, 'just want this')
   ) sub(a, b)
GROUP  BY a;
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Although the question has been answered, I would like to make it simple for people having such problem. If you are using 'Partition by' and 'Order by' on two different columns, then you must use Framing within window function just after 'Order By' to get desired result.

More information here.

JKhan
  • 1,157
  • 4
  • 14
  • 23