Re: Complex ORDER BY clauses

mcstock wrote:

>Daniel! You handed out a fish with no reference to acquiring fishing skills!
>Getting soft in your old age?
>
>-- mcs
>"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
>news:1068748539.612491_at_yasure...
>
>
>>Dave Rudolf wrote:
>>
>>
>>
>>>Hi all,
>>>
>>>Suppose that I have a column in a table, and I want to sort it so that
>>>certain values come first, but the sorting is not lexographical. For
>>>example, if I have a rediculously simple table, that has only one column,
>>>like so:
>>>
>>>'A'
>>>'B'
>>>'C'
>>>'D'
>>>'E'
>>>
>>>I want to sort the table so that Bs and Es come first, like so:
>>>
>>>'B'
>>>'E'
>>>'A'
>>>'C'
>>>'D'
>>>
>>>Is there some way to do such a thing?
>>>
>>>
>>>
>>>Dave
>>>
>>>
>>>
>>>
>>>
>>SELECT col
>>FROM table
>>ORDER BY decode(col, 'B', 1, 'E', 2, 'A', 3, 'C', 4, 'D', 5, 999);
>>
>>--
>>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)
>>
>>
>>

Actually if you look closely at what I wrote you will see that it leads toward the requested
answer but does not actually solve the problem correctly.

The OP wanted just the B and E and the rest to flow alphabetically. I intentionally did
something that would require a DECODE listing of every possible letter. For the OP to
use my solution the OP would need to modify it.

So not quite as soft in the head as it may appear. Though perhaps a bit too close.

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