Return Records 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 2000 Forums

Transact-SQL (2000)

Return Records with MAX Date

Author

Topic

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-08 : 11:34:30

I have two tables

tbl_Courses
tbl_Courses_Reviews

This query has all the records I need returned.

SELECT r.ReviewID, r.ReviewDate, c.CourseName, c.City, c.CourseID, c.State, c.Score, c.TeeTimePhone, r.Total_Num
FROM tbl_Courses c
LEFT JOIN tbl_Courses_Reviews r ON r.CourseID = c.CourseID

I need change the query to pull in the MAX date for the records. Meaning each course could have been reviewed multiple times but I want to retrieve the latest review date... or the last review of the course.

I have this....

SELECT r.ReviewID, r.ReviewDate, c.CourseName, c.City, c.CourseID, c.State, c.Score, c.TeeTimePhone, r.Total_Num
FROM tbl_Courses c
LEFT JOIN tbl_Courses_Reviews r ON r.CourseID = c.CourseID
where r.ReviewDate = (SELECT MAX(r2.ReviewDate) FROM tbl_Courses_Reviews r2
WHERE r.ReviewID = r.ReviewID)

But this only retrieves a single record? Thanks for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-08 : 11:47:37

WHERE r.ReviewID = r2.ReviewID

Peter Larsson
Helsingborg, Sweden

JimAmigo
Posting Yak Master

119 Posts

Posted - 2007-04-08 : 19:43:12

Peso thanks for your reply and it's gotten me half way there.

Currently I have..

SELECT c.CourseName, c.City, c.CourseID, c.State, r.ReviewDate,
c.Score, c.Review, c.TeeTimePhone, r.Total_Num, r.ReviewID
FROM tbl_Courses c
LEFT JOIN tbl_Courses_Reviews r ON r.CourseID = c.CourseID
where c.State = 'MD' OR r.ReviewDate is null AND r.ReviewDate = (SELECT MAX(r2.ReviewDate) FROM tbl_Courses_Reviews r2
WHERE r.ReviewID = r2.ReviewID)

This returns the results I need which include courses that don't have a review date. Was just wondering if this wes the most efficent way of doing this. Just trying to speed up the query.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-09 : 03:08:18

SELECT c.CourseName, c.City, c.CourseID, c.State, r.ReviewDate,
c.Score, c.Review, c.TeeTimePhone, 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.State = 'MD'

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources