Re: Complex ORDER BY clauses

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