Select Max? - 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 2000 Forums

Transact-SQL (2000)

Select Max?

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-28 : 10:48:33

I have this query that I'm trying to return ONE record but it returns two. Basically repeats.


SELECT c.CourseName, c.City, c.Score, c.CourseID, c.State,
c.Score, c.Review, c.TeeTimePhone, r.ReviewDate,  r.Total_Num, r.ReviewID 
FROM tbl_Courses c 
LEFT JOIN tbl_Courses_Reviews r ON r.CourseID = c.CourseID 
where c.CourseName Like 'Raspberry Falls Golf & Hunt Club' 
AND r.ReviewDate = (SELECT MAX(r2.ReviewDate) FROM tbl_Courses_Reviews r2 WHERE r.ReviewID = r2.ReviewID) ORDER BY c.CourseName

There could be multiple records with a corresponding courseid in tbl_Courses_Reviews. I'm trying to pull in the date of the latest review for a querystring id in my application.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-28 : 11:57:38

maybe this?


SELECT 
	c.CourseName
	,c.City
	,c.Score
	,c.CourseID
	,c.State
	,c.Score
	,c.Review
	,c.TeeTimePhone
	,max(r.ReviewDate)
	,r.Total_Num
	,r.ReviewID 
FROM tbl_Courses c 
LEFT JOIN tbl_Courses_Reviews r ON r.CourseID = c.CourseID 
where c.CourseName Like 'Raspberry Falls Golf & Hunt Club' 
group by 
	c.CourseName
	,c.City
	,c.Score
	,c.CourseID
	,c.State
	,c.Score
	,c.Review
	,c.TeeTimePhone
	,r.Total_Num
	,r.ReviewID

www.elsasoft.org

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-28 : 12:05:25

quote:

Originally posted by jezemine

maybe this?


SELECT 
	c.CourseName
	,c.City
	,c.Score
	,c.CourseID
	,c.State
	,c.Score
	,c.Review
	,c.TeeTimePhone
	,max(r.ReviewDate)
	,r.Total_Num
	,r.ReviewID 
FROM tbl_Courses c 
LEFT JOIN tbl_Courses_Reviews r ON r.CourseID = c.CourseID 
where c.CourseName Like 'Raspberry Falls Golf & Hunt Club' 
group by 
	c.CourseName
	,c.City
	,c.Score
	,c.CourseID
	,c.State
	,c.Score
	,c.Review
	,c.TeeTimePhone
	,r.Total_Num
	,r.ReviewID

www.elsasoft.org

Same result as my attempt, came back with two records. But I appreciate your attempt.

I think it's maybe another aggregate select but I don't know where it goes.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-28 : 12:06:55

Please post your table DDL, some sample data and the result that you want

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-29 : 05:37:24

[code]SELECT c.CourseName, c.City, c.Score, c.CourseID, c.State,
c.Score, c.Review, c.TeeTimePhone, r.ReviewDate, r.Total_Num, r.ReviewID
FROM tbl_Courses c
LEFT JOIN tbl_Courses_Reviews r ON r.CourseID = c.CourseID AND r.ReviewDate = (SELECT MAX(r2.ReviewDate) FROM tbl_Courses_Reviews r2 WHERE r.ReviewID = r2.ReviewID)
where c.CourseName Like 'Raspberry Falls Golf & Hunt Club'
ORDER BY c.CourseName[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources