Re: ORDER BY a previous result

From: Charles Hooper <hooperc2000_at_yahoo.com>
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

Original text of this message