I'm a little stuck on this query...

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

SQL Server Development (2000)

I'm a little stuck on this query...

Author

Topic

AdamKosecki
Starting Member

18 Posts

Posted - 2006-03-04 : 01:33:50

Hi. Got a query I'm working on here. A little background...

This is for a discussion forum not unlike the one I am posting this message on. Tables:

categories - a category is a collection of:
discussions - which is a collection of:
messages

Using this forum as an example, we are in category "Developer" and this is the first "message" of a discussion titled "I'm a little stuck on this query...". If you hit reply and help me out you will create a new message. Until there is a more recent message in a different discussion, this discussion will display at the top when you look in the category "Developer".

That's where I'm having trouble. I cannot get the query to display the correct order. This is the closest I have gotten:

SELECT d.discussionid
FROM discussions d
INNER JOIN messages m
ON m.discussionid = d.discussionid
WHERE d.categoryid = 1
ORDER BY m.cdate DESC

This will return all of the discussionid's for every message in category 1 in the correct order. But I only need each discussionid once. The one associated with the most recent message (m.cdate).

I have read a lot and exhausted my abilities. I've probably tried 10 queries, but cannot get it! Could someone tell me the keyword I'm lookin for? I don't need the answer. Just a hint!!!

Thanks,
Adam

Kristen
Test

22859 Posts

Posted - 2006-03-04 : 02:45:44

Something like this perhaps?

SELECT d.discussionid, MAX(m.cdate) AS MyMessageDate
FROM discussions d
INNER JOIN messages m
ON m.discussionid = d.discussionid
WHERE d.categoryid = 1
GROUP BY d.discussionid
ORDER BY MyMessageDate DESC

Kristen

AdamKosecki
Starting Member

18 Posts

Posted - 2006-03-04 : 17:44:25

Hi Kristin. Yea, that totally works. I thought I tried that, but what I did was slightly different. I didn't alias MAX(m.cdate), so I had this:

SELECT d.discussionid, MAX( m.cdate )
FROM discussions d
INNER JOIN messages m
ON m.discussionid = d.discussionid
WHERE d.categoryid =1
GROUP BY d.discussionid
ORDER BY m.cdate DESC

That returns the correct discussions and last message / cdate, but in the wrong order. Why is that?

Thanks,
Adam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-04 : 18:05:04

[code]SELECT d.discussionid, MAX( m.cdate )
FROM discussions d
INNER JOIN messages m
ON m.discussionid = d.discussionid
WHERE d.categoryid =1
GROUP BY d.discussionid
ORDER BY MAX(m.cdate) DESC[/code]

You did not order by MAX(m.cdate)

----------------------------------
'KH'

AdamKosecki
Starting Member

18 Posts

Posted - 2006-03-04 : 21:17:01

Ah. Makes sense. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources