How to select row with last timestamp from duplicate rows in database table?

Question

I have a table with duplicate and triple rows - how can I select duplicate rows, but have the last timestamp, as well as rows without duplication?

------------------------------------- | 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 | ------------------------------------- 

So, I get:

  ------------------------------------- | 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 | ------------------------------------- 

Answer 1

 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 

Answer 2

try it

 select * from table where some_timestamp in (select max(some_timestamp) from table group by user_id) 

Answer 3

Try this, I created SQLFIDDLE that returns the correct dataset

 SELECT * FROM YourTable AS T1 INNER JOIN ( SELECT user_id , MAX(some_timestamp) AS some_timestamp FROM YourTable GROUP BY user_id ) AS T2 ON T1.User_Id = T2.User_Id AND T1.some_timestamp = T2.some_timestamp ORDER BY 1 

http://sqlfiddle.com/#!6/f7bba/6

Answer 4

 select YourTable.* from YourTable JOIN (select User_Id, Max(Some_Timestamp) as Mx from YourTable group by User_Id) Mx on YourTable.User_Id=Mx.User_Id and YourTable.Some_Timestamp=Mx.Mx 

Answer 5

Try the following:

 select * from my_table where (user_id, some_timestamp) IN (select user_id, max(some_timestamp) from my_table group by user_id);