Re: Not ASCending nor DESCending ORDER BY

In article <35da0e7d.72668022_at_news.oanet.com>, Bob Cunningham <bcunn_at_oanet.com> wrote:
>On Tue, 18 Aug 1998 13:46:48 -0700, Kal Khatib <kkhatib_at_cisco.com>
>wrote: I have a statement
>>
>>select a, code
>>from table_a, table_b
>>where some_conditions
>>ORDER BY a, ??
>>
>>code could any letter
>>I want to order by a, ascending, and then by code in an arbitrary way
>>(eg, in this order C, W, I, L, A, X, N... etc)
>>how can I do that?
> You could use the DECODE function to alter the "code" column value
> to a sortable value. Using your example, change the C to a 1, W to
> a 2, I to a 3, etc.:
>
> SELECT a,code FROM <tables>
> WHERE <conditions>
> ORDER BY a, DECODE(code,'C',1,'W',2,'I',3,'L',4,'A',5,'X',6,'N',7,999)
>
>Bob Cunningham

If I haven't lost my memory ;-), you can't put a decode in the Order By. But you can do this:

Select a,code,
 DECODE(code,'C',1,'W',2,'I',3, ...etc) From <tables> Where <conditions>
Order by 1,3

The 1 and 3 in the order by are the relative columns in the select, 1 is "a", and 3 is the third item which is the Decode value.

Steve Cosner Received on Tue Aug 18 1998 - 23:35:41 CDT