SQL - Specific group by

Question

Suppose 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, above table has multiple entries for cav_id, How do I get the last updated (cav_id) record of each cav_id group?
Meaning, I should get following cash_id records: 1, 3, 6, 10, 11, ...

Answer 1

Try this:

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