Re: Not ASCending nor DESCending ORDER BY

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?
>
>thanks
>
>ps. please copy me when responding to group.
>

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
bcunn_at_oanet.com Received on Tue Aug 18 1998 - 18:36:30 CDT