Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!nntp2.aus1.giganews.com!border1.nntp.aus1.giganews.com!nntp.giganews.com!newsfeed1.cidera.com!Cidera!cyclone.nyroc.rr.com!cyclone-out.nyroc.rr.com!twister.nyroc.rr.com.POSTED!not-for-mail From: Steven C. Kondolf Newsgroups: comp.databases.oracle.tools Subject: Simple? SQL Question Organization: home Message-ID: X-Newsreader: Forte Agent 1.92/32.572 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 48 Date: Tue, 31 Dec 2002 23:01:42 GMT NNTP-Posting-Host: 66.24.101.160 X-Complaints-To: abuse@rr.com X-Trace: twister.nyroc.rr.com 1041375702 66.24.101.160 (Tue, 31 Dec 2002 18:01:42 EST) NNTP-Posting-Date: Tue, 31 Dec 2002 18:01:42 EST Xref: newsfeed1.easynews.com comp.databases.oracle.tools:55584 X-Received-Date: Tue, 31 Dec 2002 16:01:45 MST (news.easynews.com) 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@garbage.rochester.rr.com