CODEWITHSUNDEEP
sql
Sharhad
Sharhad

Reputation: 83

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

Upvotes: 0

Views: 602

Answers (2)

Stu
Stu

Reputation: 32599

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;

Upvotes: 2

flwd
flwd

Reputation: 613

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

Upvotes: 0

Related Questions