Re: Not ASCending nor DESCending ORDER BY

Code could be a foreign key column and in the code_lookup_table have an sort_order column attribute associated with the "code" column and join that in the table and order by the sort_order column. IF distinct values of code can be quantified in the data.

i.e. select a,code

       from table a,table_b , code_lookup_table c
     where  a.column = b.column
      and  code = c.code
     order by a,c.sort_order

The decode will also work, you can always sort by an expression (constant) check the Select SQL Command.

HTH Steve Cosner wrote in message <6rdkmu$5gq1_at_hendrix.csufresno.edu>...
>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 Thu Aug 20 1998 - 19:06:25 CDT