Select item with Max date - 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)

Select item with Max date

Author

Topic

Dobly
Starting Member

16 Posts

Posted - 2011-02-16 : 21:31:13

This one is doing my head in. I sure hope someone can help

I have 2 tables

Locations
loc_Location_ID PK

and Activity
act_Activity_ID PK
act_Location_ID FK
act_Activity_Date_End (datetime)

What I need to do is return a list of locations with the Activity date of the latest act_Date for that location.

I should note that I have tried this as a test


SELECT TOP 1 act_Activity_Date_End, 
loc_Location_ID 
FROM Location
INNER JOIN Activity on act_Location_ID = loc_Location_ID
ORDER BY act_Activity_Date_End DESC

This test works, but I already have a 'TOP' in my main query. Seems you can't use TOP twice.

I tried this as a 2nd test and it fails for that reason


SELECT 
TOP 1 pro_Prospect_ID, 
TOP 1 act_Activity_Date_End, 
loc_Location_ID 
FROM Location
INNER JOIN Activity on act_Location_ID = loc_Location_ID
INNER JOIN Prospect on pro_Location_ID = loc_Location_ID
ORDER BY act_Activity_Date_End DESC

Help please!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-16 : 22:03:52

[code]
SELECT l.loc_Location_ID, max(a.act_Activity_Date_End)
FROM Locations l
INNER JOIN Activity a ON l.loc_Location_ID = a.act_Location_ID
GROUP BY l.loc_Location_ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]