row with earliest date field - 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 2008 Forums

Transact-SQL (2008)

row with earliest date field

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-02-22 : 12:26:49

Greetings

I have about 60Millions rows in a table. The defining colum I want to use to further filter a group of similar rows is a datetime field.

How do I go about in my query requesting the earliest date of a group of rows that are identical but only differ on this datetime field?
will rownumber rank feature help me in this?

Thanks

If you don't have the passion to help people, you have no passion

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-02-22 : 12:54:06

Yes, rownumber will work.


(Select ID, DateYouWantToUse     
from
(Select ID, DateYouWantToUse,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DateYouWantToUse DESC) AS RowNum
from Table1) AS T 
where DateYouWantToUsee >= dateadd(day,datediff(day,0,getdate())-1,0) and RowNum = 1)

This will give you the most recent record within the last 24 hours.

EDIT: Missed some code in cut-n-paste fixed, sorry.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-02-22 : 13:04:52

yaaaaaaaaaaaaay! works awesome!

Thanks very much!!

If you don't have the passion to help people, you have no passion

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-02-22 : 13:07:05

Glad I was able to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources