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 18.104.22.168.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)||',');
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: