specifying ORDER BY with a fixed order

[bberg@rtnyc.com]

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.

[marist89]

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

[akkerend]

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

[marist89]

[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.

[bberg@rtnyc.com]

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.

[kmesser]

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