我有一张重复的桌子和三重行 – 如何选择重复但具有最新时间戳以及未加工行的行？
------------------------------------- | pk_id | user_id | some_timestamp | |-------------------------------------| | 1 | 123 | 10-Jun-12 14.30 | | 2 | 123 | 19-Jun-12 21.50 | | 3 | 567 | 10-Jun-12 09.23 | | 4 | 567 | 12-Jun-12 09.45 | | 5 | 567 | 13-Jun-12 08.40 | | 6 | 890 | 13-Jun-12 08.44 | -------------------------------------
所以我最终得到：
------------------------------------- | pk_id | user_id | some_timestamp | |-------------------------------------| | 2 | 123 | 19-Jun-12 21.50 | | 5 | 567 | 13-Jun-12 08.40 | | 6 | 890 | 13-Jun-12 08.44 | -------------------------------------
解决方法
SELECT * FROM ( SELECT pk_id,user_id,some_timestamp,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY some_timestamp DESC) col FROM table) x WHERE x.col = 1
