Re: Complex ORDER BY clauses

mcstock wrote:

> why the double order by?
>
> --mcs
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1068833612.356807_at_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)
> |
>

The original wanted everything ordered with B and E pulled out. The entire set must be ordered first. The outer query only changes the value of the two selected values.

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