I have a table (tbl1) with fields (ID, date, description)
I would like to select the most recent record for each ID.
So for example if there are 5 records like this
ID | date | description
I would like my query to return
1 2010-05-18 newest note
I'm not sure how to combine 'Top' and 'distict' to get only the latest result for each unique id number. Thanks.
There are two ways that come to my mind now:
First way as you see, uses row_number() ranking function in subquery, and outer query selects only rows with rn equal 1. This approach has the advantage of ability to return top x rows from particular note. Second way uses derived table query that returns noteid and max date for this note. Then you join it to testnotes table to get other fields, in this case description. You have to join by noteid and notedate to avoid situation when two notes have the same update date and result in incorrect join output.
answered May 18 '10 at 02:47 PM
Starting from SQL server 2005 you can use ROW_NUMBER to solve your problem like this:
SELECT ID, date, description FROM (SELECT ID, date, description, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY date DESC) AS row_num FROM tbl1) AS T WHERE row_num = 1
answered May 18 '10 at 02:48 PM