Page 1 of 1

Join Order By...?

Posted: Thu Jul 02, 2009 1:30 pm
by jackpf
Good evening all,

Ok, I'm making a forum for my site, and in order to count all stats, how many posts each thread has etc, I use a query like:

Code: Select all

SELECT F.*, COUNT(P.`ID`) AS `PostCount`, P.`Subject` AS `LastPost` /*<- not working!*/
FROM `Forum` F
INNER JOIN `Forum` P ON P.`Thread`=F.`ID`
WHERE F.`Type`='thread'
GROUP BY F.`ID`
ORDER BY `ID` DESC
This works fine, however, I'd like to grab the subject of the last post from the join, and display it.

If the join was ordered by `ID` DESC then the last post would be in `LastPost`, however, I can't figure out how to order the join, or if that's even possible. I've searched all over google...

Any help would be much appreciated :)

Thanks,
Jack.

Re: Join Order By...?

Posted: Thu Jul 02, 2009 3:11 pm
by VladSun
Please, have a deep look into http://dev.mysql.com/tech-resources/art ... myths.html
It will help you approve you SQL skills, especially with using GROUP BY.

In short: you can not use ORDER BY to order result prior GROUP BY in your query. Also you need to specify which table ID you use in your ORDER BY clause, because now it's ambiguous - is it P.id or F.id?

You may try to join an ordered subselect instead of a table.

Re: Join Order By...?

Posted: Thu Jul 02, 2009 9:07 pm
by jackpf
Yeah...I didn't think so.

And yeah, that's not the actual query - I just wrote it as an example...the real one's a bit more complex that probably wouldn't make a lot of sense.

Well, at the moment, I just select MAX(P.`Date`) AS `LastPost` because the highest date will be the last post...but I then need another query to get the last post's data.

And I had a go with a subselect, but I can't reference F.`ID` in the subquery (which I need to in order to count stats for that particular thread) so I'm a bit stuck.

I'll have another read around...and have a look at that link. I have a feeling I'll just have to leave it as two queries though...

Ahh well. Thanks for your help :)