Simple? SQL Question

From: Steven C. Kondolf <skondolf_at_garbage.rochester.rr.com>
Date: Tue, 31 Dec 2002 23:01:42 GMT
Message-ID: <sf741v0ltobujiuqekg9ieihn14mam3t0v_at_4ax.com>

[Quoted] We are developing a report w/reports 2.5 and Oracle 8.0.5 and need determine the "top 50" customers based total yearly sales using the following table layout:

custno Date Sales

1		200201		500
1		200201		1000
1		200202		600
2		200201		700
2		200202		900

.
.
.

We can do a simple select with a group by clause such as:

select custno, sum(sales)
from table
where date between 200201 and 200212
order by sum(sales) desc
group by custno;

And get a list of ALL customers sorted in descending sales$ order but we're having trouble generating the proper data for the "top 50" portion.

I know I can create a view summarizing the data and then do a simple select using rownum<51 but we really don't want to create a temp table or view for this purpose if we can get around it.

I've seen suggestions using a sub-select as follows:

select custno, sum(sales)
from (select custno, sum(sales)

          from table
          where date between 200201 and 200212
          group by custno
          order by sum(sales) desc)

where rownum<51;

But I get an error about a "missing right parenthesis" on the order by line in the sub-select

Any suggestions how this can be accomplished. Peace,
Steve
skondolf_at_garbage.rochester.rr.com Received on Wed Jan 01 2003 - 00:01:42 CET