SQL - 特定分组依据

Question

假设下表，

 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

如您所见，上表有多个 cav_id 条目，如何获取每个 cav_id 组的最后更新的 (cav_id) 记录？
这意味着，我应该获得以下 cash_id 记录:1, 3, 6, 10, 11, ...

Answer 1

试试这个:

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