x

select only the last result

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
1 | 2010-05-18 | newest note
1 | 2010-05-17 | other note
1 | 2010-05-16 | oldest note
2 | 2010-05-17 | newest note
2 | 2010-05-16 | oldest note

I would like my query to return

1 2010-05-18 newest note
2 2010-05-17 newest note

I'm not sure how to combine 'Top' and 'distict' to get only the latest result for each unique id number. Thanks.

more ▼

asked May 18 '10 at 02:09 PM in Default

tony 1 1 gravatar image

tony 1 1
5 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

There are two ways that come to my mind now:

create table testnotes            
(            
noteid int,             
notedate datetime,            
description varchar(20)            
)            
go            
insert testnotes(noteid, notedate, description)            
select 1, '20100501', '1st oldest note' union all            
select 1, '20100502', '1st newer note' union all            
select 1, '20100503', '1st newest note' union all            
select 2, '20100504', '2nd oldest note' union all            
select 2, '20100505', '2nd newer note' union all            
select 2, '20100506', '2nd newest note'             
go            
--first way:            
select noteid, notedate, description            
from            
(            
    select noteid, notedate, description,             
    row_number() over(partition by noteid order by notedate desc) rn            
    from testnotes            
) q            
where rn = 1            
go            
--second way            
select a.noteid, a.notedate, a.description            
from testnotes a inner join             
    (select max(notedate) notedate, noteid             
    from testnotes group by noteid) b on a.noteid = b.noteid and a.notedate = b.notedate            
go            
drop table testnotes            
go            

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.

more ▼

answered May 18 '10 at 02:47 PM

Piotr Rodak gravatar image

Piotr Rodak
262 2

thanks for the assist!
May 20 '10 at 04:35 PM tony 1 1
you're welcome. yet the idea is to vote and accept good answers, like on stack overflow. and it's not about this particular response, but rather general remark :)
May 21 '10 at 05:10 PM Piotr Rodak
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered May 18 '10 at 02:48 PM

malpashaa gravatar image

malpashaa
404 3

thanks for the input, it worked great!
May 20 '10 at 04:35 PM tony 1 1
You are welcome
May 21 '10 at 05:20 AM malpashaa
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x57
x10

asked: May 18 '10 at 02:09 PM

Seen: 1429 times

Last Updated: May 18 '10 at 02:15 PM