CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-grouphaving-clause
John Alexander Betts
John Alexander Betts

Reputation: 5206

Get rows with the maximum date from a query

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?

Upvotes: 1

Views: 1013

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

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:

Upvotes: 0

Don Chambers
Don Chambers

Reputation: 4251

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?

Upvotes: 1

Arron
Arron

Reputation: 916

This might work ;)

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

Upvotes: 0

Related Questions