Re: Complex ORDER BY clauses

> it seems this would be a lot more straight forward:
>
> select test_col
> from t
> order by DECODE(test_col, 'B', 1, 'E', 2, 99), test_col
>
> haven't done timings on large tests, but one sort certainly seems a lot
> better than 2, does standard SQL or Oracle guarantee that the sorted values
> in the inner query are not reordered by the outer sort? i can't imagine
> that's realiable behavior
>
> -- mcs

Doesn't work ... here's why.

CREATE TABLE t (test_col VARCHAR2(1));

INSERT INTO t VALUES ('D');
INSERT INTO t VALUES ('A');
INSERT INTO t VALUES ('C');
INSERT INTO t VALUES ('B');
INSERT INTO t VALUES ('E');

Now try your statement ... well actually mine as I posted it first for the OP.

Remember you can never assume ordered data in a heap table.

-- 
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)