Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Complex ORDER BY clauses
mcstock wrote:
> daniel, the confusion that you've introduced is you are advocating a
> subquery with an order by, then a second order by in the outer query, and
> relying on oracle not to change the order of rows that meet the default case
> in the DECODE order by ....
>
> you're doing two order by's (two sort operations) when one will suffice, for
> no apparent advantage...
>
> SQL> SELECT test_col
> 2 FROM (
> 3 SELECT test_col
> 4 FROM t
> 5 ORDER BY test_col)
> 6 ORDER BY DECODE(test_col, 'B', 1, 'E', 2, 99);
> ...
>
Whoa wait a minute. I'm not advocating any subquery. I am advocating an in-line view and that is quite a different thing.
I am also stating that it produces the correct result something that the single ordering provably can not do. Explain plan has its value for many things. I run them all the time. But they don't substitute for looking at the result set and seeing the desired result. You must feed the information to the DECODE already sorted for the result to be correct unless you want to introduce a pseudo-column.
-- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan_at_x.washington.edu (replace 'x' with a 'u' to reply)Received on Sat Nov 15 2003 - 20:54:41 CST