0

I have a SQL table with the following columns:

id | created_at

I want to get a dictionary of all the unique id's and the latest created_at time.

My current query is this:

SELECT id, created_at from uscis_case_history
ORDER BY created_at DESC
LIMIT 1

This gives me the latest id, but i want one for all unique id

Sharhad
  • 63
  • 8

2 Answers2

2

Sounds like you just need to aggregate:

SELECT id, MAX(created_at) LatestCreated_at
FROM uscis_case_history
GROUP BY id
ORDER BY MAX(created_at) DESC;
Stu
  • 30,392
  • 6
  • 14
  • 33
0

You can use row_number to index the dates based on their id and then filter by that:

SELECT *
  FROM (SELECT id,
               created_at,
               row_number() over (partition by id order by created_at desc) rn
          FROM tbl) a
 WHERE rn=1

Here is an example on dbfiddle

flwd
  • 548
  • 3
  • 12