Select most recent rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select most recent rows

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-11 : 11:44:25

Hi All,

I want a query to identify rows with the most recent entry for each Unique reference.

My table contains three main fields:

Reference--------------Date---------------Action
1234------------10/12/2007 15:00---------Updated
1234------------10/12/2007 17:00---------Deleted
1235------------10/12/2007 16:23---------Updated
1235------------10/12/2007 16:29---------Updated

In this scenario I would want the query to return:

1234------------10/12/2007 17:00---------Deleted
1235------------10/12/2007 16:29---------Updated

In the table there could be many actions on the same reference number, I just want to return 1 row for each reference number showing the date and most recent Action.

Can anyone help - I feel like this is so annoyingly simple!

Thanks in advance
Humate

mfemenel
Professor Frink

1421 Posts

Posted - 2007-12-11 : 11:57:52

Something like this would work for you.

select base.reference,base.mydate,tablename.action
from(
select reference,max(date) as mydate,
from tablename
group by reference)base
inner join tablename on base.[date]=tablename.[date] and base.reference=tablename.reference

Mike
"oh, that monkey is going to pay"

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-11 : 15:02:38

hmm... I get an error near 'from' when trying to use that code.

Select REF,dateref,Outcome
From (select ref, max(dateref), from table1 group by ref) as base
Inner Join table1 on base.[dateref]=[table1].[dateref] and base.ref=[table1].ref

Is that how you meant?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 15:15:15

SELECT Reference, Date, Action
FROM (SELECT Reference, Date, Action, ROW_NUMBER() OVER (PARTITION BY Reference ORDER BY Date DESC) AS RecID
) AS d
WHERE RecID = 1
ORDER BY Reference

E 12°55'05.25"
N 56°04'39.16"

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-11 : 16:53:04

I solved the above problem and used the example from mfemenel, which works great, many thanks!

Peso - I will try your method too, looks interesting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources