Find Max Row - 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)

Find Max Row

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-05-17 : 02:40:20

I have this query to find the latest completed job date, however it doesnt always find the last, and can return more than 1 result, why cant I get one result returned


SELECT     P.PropID, MAX(ASV.completion_date) AS LatestCP12Date, P.Area, COUNT(DISTINCT P.UPRN) AS CP12Count
FROM         dbo.servicing_jobs AS ASV INNER JOIN
                      dbo.vwProperties AS P ON ASV.prop_seql = P.PropID
GROUP BY P.PropID, P.Area, ASV.completion_date
HAVING      (MAX(ASV.completion_date) IS NOT NULL)

Or is there a better way to do this?

Sachin.Nand

2937 Posts

Posted - 2010-05-17 : 02:51:59

Because your PropId and Arear are distinct.Also you have done a group by clause on completion_date
which is unnecessary.

PBUH

JezLisle
Posting Yak Master

132 Posts

Posted - 2010-05-17 : 04:02:38

Excellent thanks for that :-)

Works perfectly now!!

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources