1

I have the data as below in my table in SQL Server database.

Query select * from mytable order by UpdatedDate Desc returns:

ID  COL1    UPDATED DATE
37  NULL    2018-06-25 18:20:50.210
37  NULL    2018-06-24 18:22:31.230
38  NULL    2018-04-03 14:16:47.953
38  NULL    2018-04-02 14:17:42.660
38  NULL    2018-04-01 14:17:55.963
44  NULL    2018-06-25 18:13:31.780
44  NULL    2018-06-23 18:13:44.020
44  NULL    2018-06-21 18:13:53.003
44  NULL    2018-06-20 18:14:01.490

What query will return me the result below, i.e second latest updated records

37  NULL    2018-06-24 18:22:31.230
38  NULL    2018-04-02 14:17:42.660
44  NULL    2018-06-23 18:13:44.020
Salman A
  • 262,204
  • 82
  • 430
  • 521
Phanindra Gopishetty
  • 161
  • 1
  • 8

1 Answers1

4

Just find records with row number = 2. For latest records you need to order by date desc.

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY UPDATED_DATE DESC) AS rn
    FROM yourdata
) x
WHERE rn = 2
Salman A
  • 262,204
  • 82
  • 430
  • 521