PSQL given a table with cols id, name, date return id whose most recent date is with name 'John'

Question

PSQL given a table with cols ids, name, date return id whose most recent name is 'John'. It's a little hard to describe so I have a table below:

id	name	date
1	'John'	'2020-01-01'
1	'Steve'	'2025-01-01'
2	'John'	'2021-01-01'
2	'Steve'	'2020-01-01'
3	'John'	'2022-01-01'
3	'Steve'	'2020-01-01'
4	'John'	'2020-01-01'
4	'Steve'	'2022-01-01'

Result: Ids whose most recent name is John.

id	name	date
2	'John'	'2021-01-01'
3	'John'	'2022-01-01'

I've tried using subquery. I've tried using desc limit 1 and desc limit 1 with subqueries. I can't seem to get desc limit 1 for each of the ids.

I've been trying to do this for like 2 days.

Answer 1

One option to get the intended result is to use ROW_NUMBER in a CTE:

WITH sorted AS
  (SELECT 
    id, 
    thename, 
    thedate,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY thedate DESC) AS rn
  FROM yourtable)
SELECT 
  id, thename, thedate 
FROM sorted
WHERE
  thename = 'John' AND rn = 1
ORDER BY 
  id;

The subquery within the CTE will build a column with an increasing number sorted by latest date first.

Then in the outer query we filter out all rows with another name and all rows where the number is not 1.

Thus, only those rows per id remain where the name is John and no later date with another name exists.

Side note: I renamed the columns in my answer because name and especially date are often key words which can cause issues. It's recommended to do not use key words as table name or column name.

Try out on this sample fiddle