Jump to content

Select ORDER BY (another select query) - howto?


ryel01

Recommended Posts

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?
Link to comment
Share on other sites

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\" /]
Link to comment
Share on other sites

[!--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
Link to comment
Share on other sites

[!--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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.