Re: Complex ORDER BY clauses

"mcstock" <mcstockspamplug_at_spamdamenquery.com> schrieb im Newsbeitrag news:iOGdnR9WG4atNyqi4p2dnA_at_comcast.com...

> In your earlier post your ORDER BY only included the DECODE expression, it
> did not include the secondary sort expression, which is why it would fail

Oh, I must admit I overlooked the second sort expression. So your originally posted code mcstock is working fine for me:

select * from t;

1 A
2 B
3 C
4 D
5 E
6 H
7 G
8 F

SELECT col
FROM t
ORDER BY DECODE(col,'B',1,'E',2,99), col;

1 B
2 E
3 A
4 C
5 D
6 F
7 G
8 H

But still the query from Daniel Morgen is not working for me:

SELECT col
FROM (
  SELECT col
  FROM t
  ORDER BY col)
ORDER BY DECODE(col, 'B', 1, 'E', 2, 99);

1 B
2 E
3 A
4 F
5 H
6 G
7 C
8 D

I think Jonathan Gennick already mentioned that the outer sort may not only change the positions it must to fulfill the order clause:

>You cannot rely on the first sort affecting the second. The two sorts
>are independent. Depending on the first sort to affect the second is
>like depending on the order of insertion into a table to affect the
>order returned by a SELECT.

--
Matthias Wirtz  -  Norfolk, USA