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

Thank you in advance.

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

On 17 feb, 11:31, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com> wrote:
Dessuported-unpatched DB version.

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

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

Cheers.

Carlos.

Carlos escribió:
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 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.


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

=?ISO-8859-1?Q?=22=C1lvaro_G=2E_Vicario=22?= (alvaro.NOSPAMTHANX (AT) demogracia (DOT) 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.

On Feb 17, 5:31*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com> wrote:
No need to make this too difficult. If you were on 10g, you could so
some fancy things with regexp_substr. A simple example which should
work on 9i and below:
Create a testing table for this demonstration named T1, think of this
as your FOO table:
CREATE TABLE T1 AS
SELECT
ROWNUM C1
FROM
DUAL
CONNECT BY
LEVEL<=100;

Now the first step, just retrieve the rows you want:
SELECT
C1
FROM
T1
WHERE
C1 IN (1,98,12,33);

C1
----------
1
12
33
98

Now, sort the rows:
SELECT
C1
FROM
T1
WHERE
C1 IN (1,98,12,33)
ORDER BY
INSTR('1,98,12,33,' , TO_CHAR(C1)||',');

C1
----------
1
98
12
33

Note in the INSTR, the sequence of the numbers must end in a comma,
and we tell INSTR to locate the number in the list with a comma
appended to the end of the value of C1.

Your SQL statement would look like this:
SELECT .......
FROM FOO
LEFT JOIN BAR .......
WHERE FOO_ID IN (1, 98, 12, 33)
ORDER BY
INSTR('1,98,12,33,' , TO_CHAR(FOO_ID)||',');

Or:
SELECT .......
FROM
FOO,
BAR
WHERE
FOO.FOO_ID=BAR.FOO_ID(+)
AND FOO_ID IN (1, 98, 12, 33)
ORDER BY
INSTR('1,98,12,33,' , TO_CHAR(FOO_ID)||',');

Before deciding to use the above technique, determine if there is a
better way to do *everything* in a single SQL statement. You might be
able to do this by wrapping your complicated SQL statement into an
inline view, and joining to that just as if it were a regular table:
SELECT .......
FROM
FOO,
BAR,
( complicated SQL here ) V
WHERE
V.FOO_ID=FOO.ID
AND FOO.FOO_ID=BAR.FOO_ID(+)
ORDER BY
V.RN;

The RN column would be generated inside the inline view V, possibly
like this, if there is an ORDER BY clause in the inline view:
ROWNUM RN

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Feb 17, 5:31*pm, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote:
Small correction to my post - about 10 minutes after posting, I
thought what if...

To avoid matching only the last digit of a two digit number, or the
last two digits of a three digit number, etc. the list must also begin
with a comma, and the FOO_ID column must have a comma embedded at the
beginning inside the INSTR function.
This:
INSTR('1,98,12,33,' , TO_CHAR(FOO_ID)||',')

Becomes:
INSTR(',1,98,12,33,' , ','||TO_CHAR(FOO_ID)||',')

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.