Re: ORDER BY a previous result

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com>
Date: Tue, 17 Feb 2009 12:42:59 +0100
Message-ID: <gne7s6$ip2$1_at_news.motzarella.org>

Carlos escribió:
>> 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?
>>
>> Server runs Oracle9i Enterprise Edition Release 9.2.0.1.0

> Dessuported-unpatched DB version.

I know. It also lacks several basic features I'd happily use, such as regular expressions. If it depended on me I would never work with Oracle 9, MySQL 3, PHP 4 or Internet Explorer 6: I'm not a masochist.

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

>
> Wrong design: better one query that does all the work.

I don't think so. They hardly have any logic in common. One application module allows you to filter and sort items using all kind of fancy rules. Another application module allows you to generate custom views on selected items. Keeping it separate simplifies SQL a lot and improves performance greatly; I know because I tried the other approach first. The second query doesn't need to know about the extremely complex filter rules.

>> Would it be possible to use the ID list to sort the
>> second query inside Oracle?

> 
> Yes. You should extract the ID and the order you want and re-write the
> second query to use an ORDER BY clause on the 'order' column.
> 
> SELECT .......
> FROM FOO a
> LEFT JOIN BAR b.......
> ON a.FOO_ID = b.FOO_ID
> ORDER BY b.MY_ORDER

If I understand correctly, your suggestion is creating a (temporary?) table with the order, isn't it? I'll look into it, thank you.

-- 
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://bits.demogracia.com
-- Mi web de humor al baño María: http://www.demogracia.com
--

Received on Tue Feb 17 2009 - 05:42:59 CST