Re: ORDER BY a previous result
Date: Tue, 17 Feb 2009 14:31:22 -0800 (PST)
Message-ID: <ee8dd30c-3a7f-4f82-8df5-ca2fa251b609_at_n30g2000vba.googlegroups.com>
On Feb 17, 5:31 am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH..._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?
>
> Server runs Oracle9i Enterprise Edition Release 9.2.0.1.0
>
> Thank you in advance.
>
> --
> --http://alvaro.es- Álvaro G. Vicario - Burgos, Spain
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.
Received on Tue Feb 17 2009 - 16:31:22 CST