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: mcstock <mcstockspamplug_at_spamdamenquery.com>
Date: Fri, 14 Nov 2003 18:12:56 -0500
Message-ID: <vKidnQKZ6-Ae_Cii4p2dnA@comcast.com>

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1068849441.169822_at_yasure...
| 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)
|

it seems this would be a lot more straight forward:

select test_col
from t
order by DECODE(test_col, 'B', 1, 'E', 2, 99), test_col

haven't done timings on large tests, but one sort certainly seems a lot better than 2, does standard SQL or Oracle guarantee that the sorted values in the inner query are not reordered by the outer sort? i can't imagine that's realiable behavior

Received on Fri Nov 14 2003 - 17:12:56 CST

Original text of this message

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