Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Complex ORDER BY clauses

Re: Complex ORDER BY clauses

From: Hans Weil <hans.weil_at_web.de>
Date: Fri, 14 Nov 2003 10:30:58 +0100
Message-ID: <bp27ci$1j30uh$1@ID-12190.news.uni-berlin.de>


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 Received on Fri Nov 14 2003 - 03:30:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US