'SQL Query to get the last unique value from a column

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

sql


Solution 1:[1]

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;

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 flwd

Related Questions