Group By?! - 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)

Group By?!

Author

Topic

Guy007
Starting Member

6 Posts

Posted - 2007-08-24 : 04:52:00

I have a table with the following columns:

ID (int) Primary Key
Date (datetime)
RefNo (varchar(9))
Description (varchar(500))

Sample data:
0, 2007-09-10 00:00:00, 'ABC', 'description0'
1, 2007-09-05 00:00:00, 'ABC', 'description1'
2, 2007-09-10 00:00:00, 'XYZ', 'description2'
4, 2007-09-06 00:00:00, 'XYZ', 'description4'

Now i want to do the following:
For each *different* RefNo in the table
Return the row that has the *most recent* date

that is, i get:
0, 2007-09-10 00:00:00, 'ABC', 'description0'
2, 2007-09-10 00:00:00, 'XYZ', 'description2'

How can i do it! I'm lost :(

thanks!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-24 : 05:04:40

[code]SELECT mt.ID,
mt.Date,
mt.RefNo,
mt.Description
FROM ( SELECT mt.ID,
mt.Date,
mt.RefNo,
mt.Description,
ROW_NUMBER() OVER(PARTITION BY RefNo ORDER BY Date DESC) AS RowNum
FROM dbo.MyTable AS mt
) AS mt
WHERE mt.RowNum = 1
[/code]

Mark

Guy007
Starting Member

6 Posts

Posted - 2007-08-24 : 05:24:43

quote:

Originally posted by mwjdavidson

SELECT  mt.ID, 
        mt.Date, 
        mt.RefNo, 
        mt.Description
FROM    (   SELECT  mt.ID, 
                    mt.Date, 
                    mt.RefNo, 
                    mt.Description, 
                    ROW_NUMBER() OVER(PARTITION BY RefNo ORDER BY Date DESC) AS RowNum
            FROM    dbo.MyTable AS mt
        ) AS mt
WHERE   mt.RowNum = 1

Mark

wow thanks! i was trying to do it all wrong, using group by instead of partitions! :)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-24 : 05:32:39

Prior to SQL Server 2005, that would've been the way to go (aggregate query in a derived table joined to your source table). Using ranking functions is a bit neater though!

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources