CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Lennie
Lennie

Reputation: 2447

Select recond by max date return latest record

In PostgreSQL database i have a table that have 2x records with the same foreign key but the date/time is different, i want todo a select in a LEFT JOIN sub-query to return only the max record (the latest one). The ON clause will then be for the foreign key.

Foreign Key | date_created
1           | 2017-11-24 17:22:01
1           | 2017-11-23 17:32:20

I want the latest record being the one for 2017-11-23 17:32:20

What I tried:

SELECT sa.foreign_key, max(date_created) as date_created
FROM table sa
GROUP BY sa.foreign_key, sa.date_created

It still return both records and not the very latest one only.

Upvotes: 0

Views: 63

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

You could use ROW_NUMBER:

SELECT *
FROM (SELECT *, 
      ROW_NUMBER() OVER(PARTITION BY sa.foreign_key 
                        ORDER BY sa.date_created DESC) AS rn
      FROM table sa) sub
WHERE rn = 1;

Upvotes: 1

Related Questions