Want only one row with the most recent date

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

General SQL Server Forums

New to SQL Server Programming

Want only one row with the most recent date

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-01-12 : 18:08:26

Hi,

I am trying to get only one (matching row) from this query with the most recent date value.
If I do like -
SELECT DATE, STATUS_ID FROM FILEHISTORY_INFO
WHERE FILE_NAME = 'ABCD.XML'
GROUP BY DATE, STATUS_ID
HAVING DATE = MAX(DATE)

DATE STATUS_ID
---- ---------
2010-01-11 13:24:08.853 04
2010-01-11 13:40:19.107 06
2010-01-11 13:22:22.200 07
2010-01-06 15:12:48.917 11

Then I get several rows with different dates as you can see. If I use TOP 1 - I get the first matching row in the table.

How can I do this?
Thanks.

sqlbug

jcampbell
Starting Member

9 Posts

Posted - 2010-01-12 : 18:25:02

Not sure what you mean one matching row. If you want to get the most recent record for your where clause you can do this

SELECT top 1 DATE, STATUS_ID FROM FILEHISTORY_INFO
WHERE FILE_NAME = 'ABCD.XML'
ORDER BY DATE DESC

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-01-12 : 18:34:36

Ooooh....Thanks, I'm pretty dumb at times.

jcampbell
Starting Member

9 Posts

Posted - 2010-01-12 : 20:37:20

no problem, glad I could help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources