Select ORDER BY (another select query) - howto?

[ryel01]

Hello!

Can someone help me work out the following - hopefully the code below will sort of explain itself as to what I'm trying to do?

[b]I want to:[/b]

SELECT * FROM messageboard_links WHERE fatherid = '0' ORDER BY ?????????

[b]Ordered by the results of this query:[/b]

SELECT id FROM messageboard_links WHERE fatherid != '0' AND categoryid = '$categoryid' ORDER BY date DESC



[b]This is what I've got so far, but obviously the order by isn't working (or I wouldn't have to ask :) )
[/b]

$query = "SELECT * FROM messageboard_links WHERE fatherid = '0' ORDER BY ( SELECT id FROM messageboard_links WHERE fatherid != '0' AND categoryid = '$categoryid' ORDER BY date DESC )";


Any ideas?

[rburch]

You can have a subquery on the end of ORDER BY, only on WHERE x =

Your ORDER BY needs to be one of the column names in the database.

By the way, I'm confused by you say you want fatherid != '0' then fatherid = '0' Could you give more info on the column names and the needed result? There may be a better way to go about what you are trying to acheive... [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /]

[ryel01]

[!--quoteo(post=374201:date=May 16 2006, 06:01 PM:name=Advisor247)--][div class=\'quotetop\']QUOTE(Advisor247 @ May 16 2006, 06:01 PM) [snapback]374201[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Could you give more info on the column names and the needed result? There may be a better way to go about what you are trying to acheive... [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /]
[/quote]

Hi Advisor247 - Yes my post was slightly confusing :)


I have a table made up of the following:


id | int(11) auto_increment / id of this message
categoryid | int(11) / external category variable
fatherid | int(4) / id if reply, otherwise 0 if topic
title | varchar(40) / title of message
body | text / body of message
iconid | int(2) / id of icon used in message
date | int(11) / the date of the message (timestamp)
userid | int(11) / id of user who posted message


The table holds 2 types of records:

1. Top level messages (Topics) identified by fatherid = 0
2. Second level messages (replies) fatherid = id of topic

So the two levels form the "Topic" and "replies" to the topic.

What I want to do is select all of the "Topics" with categoryid = X and sort them in descending order by the most recent "reply".

So effectively the topic with the most recent reply will always be at the top of the list, the topic with the 2nd most recent reply will appear second in the list etc etc etc.

Hope that helps!?

Regan

[fenway]

You should be able to do this with a derived table, and then use the id column of this dervied table as the order by clause.

[mort]

you mean like table1.field and table2.field?

what about if you want to ORDER a query by the count() of something, or in my case, by the amount of rows a string is in the field

[fenway]

[!--quoteo(post=374427:date=May 16 2006, 05:05 PM:name=mort)--][div class=\'quotetop\']QUOTE(mort @ May 16 2006, 05:05 PM) [snapback]374427[/snapback][/div][div class=\'quotemain\'][!--quotec--]
you mean like table1.field and table2.field?

what about if you want to ORDER a query by the count() of something, or in my case, by the amount of rows a string is in the field
[/quote]
Basically, yes -- and nothing stops you from using expressions, like count(). The key is to make the dervied table and JOIN it in properly, which should be staightforward, given that it's a self-join.