Re: Complex ORDER BY clauses

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 14 Nov 2003 10:13:09 -0800
Message-ID: <1068833612.356807@yasure>

Hans Weil wrote:

> hi all,
>
> "Dave Rudolf" <dave_the_funkatron_at_hotmail.com> schrieb im Newsbeitrag
> news:vr7h4hspa1e286_at_corp.supernews.com...
>

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

>
> one way so solve is, to separate the query in two subselects with an
> aditional sort criteria joinded by UNION.
> So sort the subquery you have to use GROUP BY, because the order by clause
> is not allowed in subqueries.
> You should not try this with very large tables ;-)
>
> See the example below.
>
> SELECT 1 Sortcriteria, COLRESULT
> FROM Sample_Table
> WHERE LEFT(COLRESULT,1) in ('E','F')
> GROUP BY COLRESULT
> UNION
> SELECT 2 Sortcriteria, COLRESULT
> FROM Sample_Table
> WHERE not LEFT(COLRESULT,1) in ('E','F')
> GROUP BY COLRESULT
> ORDER BY Sortcriteria
>

Worked on this with my class last night and the optimal solution from our testing was as follows:

SELECT test_col
FROM (
    SELECT test_col
    FROM t
    ORDER BY test_col)
ORDER BY DECODE(test_col, 'B', 1, 'E', 2, 99);

Seems to be quite efficient and meets the OP's criteria.

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

Received on Fri Nov 14 2003 - 12:13:09 CST