Select id where date is null or max

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 id where date is null or max

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2007-12-20 : 14:39:51

I have a table, PersonLocation:

PersonGUID - GUID
Location - int
LocationEndDate - DateTime

Each person could have been assigned to multiple locations, the most current one either has a null end date or the latest end date. I want to select the record for each person that has a null end date and if none for that person are null then I want the one with the most recent LocationEndDate.

Thanks
Scott

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 14:46:36

SELECT PersonGUID, Location, LocationEndDate
FROM (
SELECT PersonGUID, Location, LocationEndDate, ROW_NUMBER() OVER (PARTITION BY PersonGUID ORDER BY CASE WHEN LocationEndDate IS NULL THEN 0 ELSE 1 END, LocationEndDate DESC) AS RecID FROM PersonLocation
) AS d
WHERE RecID = 1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources