CODEWITHSUNDEEP
sqlduplicates
Vering
Vering

Reputation: 919

SELECT newest entry by timestamp

If there exists rows with the same TimeStamp_2 I need the row with the newest TimeStamp_1

ID           TimeStamp_1        TimeStamp_2
------------------------------------------------
0000003259   2013-05-23 09:53   2013-05-23 09:55
0000003259   2013-05-23 09:52   2013-05-23 09:55
0000003257   2013-05-23 07:52   2013-05-23 07:53

How do I do that? (The above table is a JOIN if itself, hence several rows with same ID)

In the above example, the result set should contain this

ID           TimeStamp_1        TimeStamp_2
------------------------------------------------
0000003259   2013-05-23 09:53   2013-05-23 09:55
0000003257   2013-05-23 07:52   2013-05-23 07:53

Upvotes: 0

Views: 81

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

From the data you have provided, the simplest way would seem to be an aggregation:

select id, min(TimeStamp_1) as TimeStamp_1, TimeStamp_2
from t
group by id, TimeStamp_2;

Upvotes: 2

TechDo
TechDo

Reputation: 18659

Please try:

SELECT * FROM(
    SELECT 
     ID,
     TimeStamp_1,
     TimeStamp_2,
     ROW_NUMBER() OVER(PARTITION BY ID, TimeStamp_2 ORDER BY TimeStamp_1 DESC) AS RNum
    FROM TableName
)x
WHERE RNum=1

Upvotes: 1

Related Questions