Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Complex ORDER BY clauses

Re: Complex ORDER BY clauses

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sat, 15 Nov 2003 18:54:41 -0800
Message-ID: <1068951307.248254@yasure>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US