Gossamer Forum: General: Databases and SQL: Select ORDER BY (another select query)

May 15, 2006, 8:07 PM

ryel01

Veteran (1264 posts)

May 15, 2006, 8:07 PM

Post #1 of 3

Shortcut

Select ORDER BY (another select query) - howto?

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?

I want to:

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

Ordered by the results of this query:

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

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

$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?

r

May 25, 2006, 4:43 PM

Alex

Administrator (9387 posts)

May 25, 2006, 4:43 PM

Post #2 of 3

Shortcut

Re: [ryel01] Select ORDER BY (another select query) - howto? In reply to

Hi,

I don't quite understand what you are trying to do conceptually. You want to return a list of links in a specific order, but the id's returned from the second query won't match any of the first (as one has fatherid = 0, and the other doesn't).

Cheers,

Alex
--
Gossamer Threads Inc.

May 25, 2006, 6:29 PM

ryel01

Veteran (1264 posts)

May 25, 2006, 6:29 PM

Post #3 of 3

Shortcut

Re: [Alex] Select ORDER BY (another select query) - howto? In reply to

hi Alex

this is what I ended up with - I'm not sure if it is the best solution or not (but it seems to work). I thought there might be a way to do it without having multiple select statements in the one query?

regan

Code:

$query = " 

select topic.* , 
	coalesce(reply.date,topic.date) as latest_date, 
	( select count(id) 
		from messageboard_links 
		where fatherid = topic.id 
		or id = topic.id ) as totalreplies 
from messageboard_links as topic 
left outer join 
	messageboard_links as reply 
	on reply.fatherid = topic.id 
	and reply.date 
	= ( select max(date) 
			from messageboard_links 
			where fatherid = topic.id ) 

where topic.categoryid = $categoryid 
and topic.fatherid = '0' 
order by latest_date desc 

";