Join Order By...?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Join Order By...?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Join Order By...?

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: Join Order By...?

Post 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 :)
Post Reply