SQL - Retain Specific Sort Order

December 5, 2009

Some time ago the following question appeared in the comp.database.oracle.misc Usenet group: http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/4352a39cab5ab408

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

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