Need efficient way of writing this SELECT MAX query

Question

Post reply

Need efficient way of writing this SELECT MAX query

mel1daa

Grasshopper

Points: 18
More actions
February 22, 2008 at 12:40 am

#186914

I have created a message board application that I want to display all forums and their most recent thread (if applicable - by date) by forum. So if I have the following data:
Forum (forum_id, forumname)
1, 'Baseball'
2, 'Soccer'
3, 'Football'
Threads (thread_id, forum_id, threadname, postdate)
1, 1, 'Greatest Pitchers', 2/2/2008 12:05 AM
2, 1, 'Greatest Batters', 2/2/2008 12:05 AM
3, 2, 'Pele', 1/12/2008 11:20 AM
4, 3, 'Super Bowls', 1/2/2008 10:58 AM
I want the 'Baseball' forum to show EITHER the 'Greatest Pitchers' or 'Greatest Batters' (i don't care) but just one of them. My query that I have been using in this situation is not going to work since the date is exactly the same for these threads. What I would normally do is:
SELECT f.forumid, forumname
FROM Forums f
LEFT JOIN Threads t ON f.forum_id=t.forum_id
LEFT JOIN (SELECT forum_id, MAX(postdate) as last_postdate FROM Threads GROUP By forum_id) last_thread ON t.forum_id=last_thread.forum_id AND t.postdate=last_thread.last_postdate
This does not work because the two dates are exactly the same. How can I write this so that one or the other record will display?
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dragos_sv Default port Points: 1478 More actions · Answer 1

hi

if you do not care which of the two simultaneous posts are displayed you can use this

SELECT f.forum_id, forumname, threadname,postdate

FROM #Forum f

LEFT JOIN #Threads t ON f.forum_id=t.forum_id

JOIN (

SELECT forum_id, max(thread_id) as max_thread_id, MAX(postdate) as last_postdate FROM #Threads GROUP By forum_id

) last_thread ON t.forum_id=last_thread.forum_id AND t.postdate=last_thread.last_postdate and max_thread_id = thread_id

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Could you add a TOP 1?

SELECT TOP 1 f.forumid, forumname

FROM Forums f

LEFT JOIN Threads t ON f.forum_id=t.forum_id

LEFT JOIN (SELECT forum_id, MAX(postdate) as last_postdate FROM Threads GROUP By forum_id) last_thread ON t.forum_id=last_thread.forum_id AND t.postdate=last_thread.last_postdate

That will guarentee that you'll never get more than 1 row. No promises which one you get.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dragos_sv Default port Points: 1478 More actions · Answer 3

GilaMonster (2/22/2008)
Could you add a TOP 1?
SELECT TOP 1 f.forumid, forumname
FROM Forums f
LEFT JOIN Threads t ON f.forum_id=t.forum_id
LEFT JOIN (SELECT forum_id, MAX(postdate) as last_postdate FROM Threads GROUP By forum_id) last_thread ON t.forum_id=last_thread.forum_id AND t.postdate=last_thread.last_postdate
That will guarentee that you'll never get more than 1 row. No promises which one you get.

but only one forum will be displayed. i guess there should be just one line per forum.... not one line in total 🙂

Ian Yates SSCoach Points: 19738 More actions · Answer 4

Dragon_sv's way should give you what you want. Make sure you have an appropriate index! 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

True. I should read more carefully.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mel1daa Grasshopper Points: 18 More actions · Answer 6

Thank you ALL for your responses.

One question though. Won't this subquery

SELECT forum_id, max(thread_id) as max_thread_id, MAX(postdate) as last_postdate FROM #Threads GROUP By forum_id

return the highest thread_id for the forum_id regardless of the postdate? I can't test this right now as I am not at work. Thanks.