CODEWITHSUNDEEP
sqlsql-server
Jackson Tale
Jackson Tale

Reputation: 25812

How to get the latest rows in sql server

I have a table like this:

id      start       end
------------------------------        
1       2013-04-08  null
2       2013-04-01  null
2       2012-04-01  2013-04-01
3       2012-04-01  2013-04-01
4       2012-04-01  2013-04-01
4       2011-04-01  2012-04-01

I want to get such a table:

id  start       end
-----------------------------            
1   2013-04-08  null
2   2013-04-01  null
3   2012-04-01  2013-04-01
4   2012-04-01  2013-04-01

so I wish to get one row for each id with newest information.

How can I write the query?

Upvotes: 3

Views: 128

Answers (2)

ArchiFloyd
ArchiFloyd

Reputation: 1294

As a follow up on J W's answer, you could also do it like this, which in my eyes are easier to figure out:

SELECT * FROM (SELECT * FROM table1 ORDER BY start DESC) AS temp GROUP BY id;

or

SELECT * FROM (SELECT * FROM table1 ORDER BY start DESC) AS temp GROUP BY id ORDER BY id;

this would do the same thing, so it depends on how you like the query, the last query sorts the outcome by the ID whereas the first doesn't.

http://sqlfiddle.com/#!2/885cd/1

Upvotes: 1

John Woo
John Woo

Reputation: 263693

SQL Server supports Common Table Expression and Windowing Functions. ROW_NUMBER() ranks the records for every group and which is the basis for the filtering of he records.

WITH records
AS
(
    SELECT  ID, [Start], [End],
            ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [START] DESC) rn
    FROM    TableName
)
SELECT ID, [Start], [End]
FROM    records
WHERE   rn = 1

Upvotes: 4

Related Questions