SQL query for retrieving the last record of a group

Imagine that you have a table with multiple rows for the same entity. How to pick up the latest record for each entity?

By using the query below, you can retrieve only the latest record based on a column you specify (It’s ID column in my example).

SELECT ID, USER, AMOUNT 
FROM 
  (SELECT ID, USER, AMOUNT, rank () over (partition by USER order by ID desc) AS rnk 
  FROM ORDERS) x
WHERE x.rnk = 1

SQL Table

Leave a Reply