Re: Complex ORDER BY clauses

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)
|
Received on Fri Nov 14 2003 - 13:45:35 CST