Re: Complex ORDER BY clauses

On Sat, 15 Nov 2003 18:54:41 -0800, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

>> SQL> SELECT test_col


>>   2  FROM (

>>   3       SELECT test_col

>>   4       FROM t

>>   5       ORDER BY test_col)

I've been following this discussion for a bit, so I hope no one minds if I jump in. I fail to see the reason for the double sort in the above query. Consider, the conceptual order of execution:

1. The inline view retrieves test_col from t, and sorts the values by test_col
2. The outer SELECT retrieves the exact same set of test_col values. That they happen to be sorted in a particular order as the result of step 1 is irrelevant.
3. The outer SELECT sorts the data again.

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. There's no need for the inline view at all in this case. mcstock was correct earlier in suggestion to simply use:

select test_col
from t
order by DECODE(test_col, 'B', 1, 'E', 2, 99), test_col;

An interesting twist on this problem would be to sort B and E first, and then everything else in alphabetical order. For that, my first thought is to look at using TRANSLATE. It's a bit late, but I'll give it a bit of a go and see what I can come up with.

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body. Received on Sat Nov 15 2003 - 23:08:21 CST