Get rows with the maximum date from a query

Question

How to get all rows with the maximum date from a query in PostgreSQL?

For example, if I got the following rows:

1, 1, '2014-05-27'
2, 2, '2014-05-26'
3, 3, '2014-05-26'
4, 4, '2014-05-25'
5, 5, '2014-05-27'

I need to get this result:

1, 1, '2014-05-27'
5, 5, '2014-05-27'

Only the ones with the maximum date - but all of those. This gets it done by repeating the query in a subquery:

SELECT *
FROM table
WHERE field_1 = '1'
AND date_1 = (SELECT MAX(date_1) FROM table WHERE field_1 = '1');

Is there a simpler / faster way (without repeating the query)?
Maybe with the HAVING clause?

HAVING doesn't apply here. It is used to filter results based on the value of an aggregate, but your result set includes no aggregate. — Matt, Jul 31 '14 at 15:35

score 1 · Answer 1 · answered Jul 31 '14 at 15:59

I think this will work:

select *
from table table1 join (
    select max(date_1) theMaxDate from table) table2
on table1. date_1= table2. theMaxDate

Here we are selecting the MaxDate from the table (aliased as table2). This maxDate will be referenced as table2.theMaxDate.

Then we join with the the table (which I referenced as table1 but that's not required).

We join where the date in table1 equals the max date from table2.

I don't think you can do this with a having.

Does this work for you?

score 0 · Answer 2 · answered Jul 31 '14 at 15:37

0

This might work ;)

SELECT *
  FROM table
  WHERE field_1 = '1'
  ORDER BY date_1 DESC
  LIMIT 1 OFFSET 0

answered Jul 31 '14 at 15:37

Flip Vernooij

889
6
15

There is no need to specify `OFFSET` with every `LIMIT`. – Ihor Romanchenko Jul 31 '14 at 15:38
I need all records but not only the first one – John Alexander Betts Jul 31 '14 at 15:54

Erwin Brandstetter · Answer 3 · 2018-10-26T22:26:56.803

To get only rows for the latest date in the query use the window function rank().
You need a subquery (or CTE), because window functions are applied after WHERE and HAVING.

The HAVING clause is not applicable without because you are not aggregating rows.

SELECT *
FROM  (
    SELECT *, rank() OVER (ORDER BY date_1 DESC) AS rnk
    FROM   tbl
    WHERE  field_1 = '1'
    ) sub
WHERE  rnk = 1;

Your original query may be even faster for simple cases. Indexes on field_1 and date_1 are the key to good performance. The optimum for either query would be a multicolumn index:

CREATE INDEX foo ON tbl (field_1, date_1 DESC);

If date_1 can be NULL you may want to use NULLS LAST in index and query. See:

PostgreSQL sort by datetime asc, null first?

Get rows with the maximum date from a query

3 Answers3