[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:
ORDER BY decode(job,
Or if you want to confuse your users:
ORDER BY translate(ename, rowidtochar(rowid), to_char(sysdate+31,'SSSPMMSPHH24SP'));
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.
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...
producer prior total
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
producer total prior total
A 95 55
the query above gives like 4500 for the total AND prior total.
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