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.