SQL - Specific Group on

Question

Suppose the following table,

cash_id | cav_id | updated | status ---------+--------+-------------+--------- 1 | 1 | 2014-10-14 | A 2 | 2 | 2014-10-14 | A 3 | 2 | 2014-10-16 | B 4 | 3 | 2014-10-17 | A 5 | 3 | 2014-10-17 | B 6 | 3 | 2014-10-18 | C 7 | 4 | 2014-10-18 | A 8 | 5 | 2014-10-19 | A 9 | 5 | 2014-10-19 | B 10 | 4 | 2014-10-20 | B 11 | 5 | 2014-10-21 | c 

As you can see, the table above contains several entries for cav_id, how can I get the latest updated record (cav_id) for each cav_id group?
So I have to get the following cash_id entries: 1, 3, 6, 10, 11, ...

Answer 1

Try the following:

 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY cav_id ORDER BY updated DESC) seq FROM yourTable) dt WHERE seq = 1;