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.
answered May 18, 2010 at 02:47 PM
Starting from SQL server 2005 you can use ROW_NUMBER to solve your problem like this:
answered May 18, 2010 at 02:48 PM