SQL Query to get the last unique value from a column-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

Try "SELECT DISTINCT" The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT column1, column2, ... 
FROM table_name;

If you want to retrieve the last distinct set of records in this table based on the datetime value, try this:

SELECT * FROM [tableName] 
WHERE id IN (SELECT MAX(id) FROM [tableName] GROUP BY code)

CodePudding user response：

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