specifying ORDER BY with a fixed order
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: specifying ORDER BY with a fixed order

  1. #1
    I wish to do something like this:

    select colA, sum(colB) from tableA
    where colA in ('Cat1', 'Cat2', 'Cat5', 'Cat4', 'Cat3')
    group by colA
    order by ???


    I want the results to BE IN THE ORDER Cat1, Cat2, Cat5, Cat4, Cat3....

    Can I specify the order of the results when it is unrelated to alphabet, the resulting count, etc... basically a predecided ordering?

    thanks.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    AFAIK, no. The only way you could do this is to have a seperate table that has a column for the 'Catn' and then an order specifier. ex.

    TableB:
    colA varchar2(10),
    colOrder number(5)

    Then, you could:
    select a.colA, sum(a.colB) from tableA a, TableB b
    where a.colA in ('Cat1', 'Cat2', 'Cat5', 'Cat4', 'Cat3')
    and a.colA = b.colA
    group by colA
    order by b.colOrder


    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2000
    Posts
    296
    You can order in almost any kind of order you want, i think in your case you can use decode:

    SELECT *
    FROM emp
    ORDER BY decode(job,
    'CLERK', 0,
    'PRESIDENT', 1,
    'MANAGER', 2,
    'ANALYST', 3,
    'SALESMAN',4,
    5);

    Or if you want to confuse your users:
    SELECT *
    FROM emp
    ORDER BY translate(ename, rowidtochar(rowid), to_char(sysdate+31,'SSSPMMSPHH24SP'));

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    [QUOTE][i]Originally posted by akkerend [/i]
    [B]You can order in almost any kind of order you want, i think in your case you can use decode:

    SELECT *
    FROM emp
    ORDER BY decode(job,
    'CLERK', 0,
    'PRESIDENT', 1,
    'MANAGER', 2,
    'ANALYST', 3,
    'SALESMAN',4,
    5);

    Or if you want to confuse your users:
    SELECT *
    FROM emp
    ORDER BY translate(ename, rowidtochar(rowid), to_char(sysdate+31,'SSSPMMSPHH24SP'));
    [/B][/QUOTE]
    Use the DECODE statement sparingly. In this case, what if there were something new to sort by? You would have to change the query... Makes for a maintenance nightmare, IMHO of course.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    thanks, but we are dynamically building the decode statement based on an earlier query... see we are getting the top 10 producers, and then their prior values... but the 2nd query (prior) values should be in the same order as the prior list, no matter the prior production rate...

    ex.
    first query..

    producer total
    A 90
    D 74
    C 44
    B 11

    second query:
    producer prior total
    A 78
    D 40
    C 98
    B 65

    Without dynamically building a decode statement (in the order A, D, C, B we COULD NOT enforce the order of the second statement.

    Why I WOULD love it to get it in one query, I couldn't figure out how to join a table to itself, and get the same column twice without having them do a cartesian product.

    is it possible to get from this table:

    producer date revenue
    A 1/4/00 50
    A 1/4/00 45
    A 1/3/00 33
    A 1/3/00 22
    ..
    --this query doesn't work...
    select T1.producer, sum(T1.revenue) as total, sum(T2.revenue) as prior_total
    from tableA T1, tableA T2
    where T1.producer = 'A'
    and T2.producer = T1.producer
    and T1.date = '1/4/00'
    and T2.date = '1/3/00'
    group by T1

    and get:
    producer total prior total
    A 95 55
    ..
    ..

    the query above gives like 4500 for the total AND prior total.


    thanks.

  6. #6
    Join Date
    Aug 2000
    Posts
    462
    select producer, sum(t1rev) current_revenue , sum(t2rev) prior_revenue from (select t1.producer, sum(t1.revenue) t1rev, sum(0) t2rev from otest t1 where t1.tdate = '4-JAN-2000' group by t1.producer union select t2.producer, sum(0) t1rev, sum(t2.revenue) t2rev from otest t2 where t2.tdate = '3-JAN-2000' group by t2.producer) group by producer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width