Re: ORDER BY a previous result

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 17 Feb 2009 13:31:20 -0700
Message-ID: <499b2ca8$1_at_news.victoria.tc.ca>



=?ISO-8859-1?Q?=22=C1lvaro_G=2E_Vicario=22?= (alvaro.NOSPAMTHANX_at_demogracia.com) wrote:
: Is there a simple way to use a previous result in an ORDER BY clause?

: I have a rather complicate query that filters, sorts and returns a
: series of IDs:

: FOO_ID
: ======
: 1
: 98
: 12
: 33

: Then, I use these IDs to fetch further information about the items they
: represent:

: SELECT .......
: FROM FOO
: LEFT JOIN BAR .......
: WHERE FOO_ID IN (1, 98, 12, 33)

: I keep the two queries separate to avoid excessive complexity. I compose
: the SQL code using PHP.

: Right now, the second query comes unsorted from Oracle: I use PHP to
: sort it at a later stage in my application (my PHP skills are better
: than my SQL ones). Would it be possible to use the ID list to sort the
: second query inside Oracle?

Assuming you are building the query as you need it, and assuming the sort order is supposed to be the order in which your first query returned the ids, then build and use something like the following

	order by
		case
		when  FOO_ID=1  then 1
		when  FOO_ID=98 then 2
		when  FOO_ID=12 then 3
		when  FOO_ID=33 then 4
		else 5 -- should never be needed
		end

Other techniques are possible. Received on Tue Feb 17 2009 - 14:31:20 CST

Original text of this message