Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!cyclone2.usenetserver.com!news.webusenet.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: Re: Simple? SQL Question Organization: home Message-ID: <3ga61vk2qe9ghsonib5berui6tlolnldgo@4ax.com> References: X-Newsreader: Forte Agent 1.92/32.572 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 69 Date: Wed, 01 Jan 2003 17:55:43 GMT NNTP-Posting-Host: 66.24.101.160 X-Complaints-To: abuse@rr.com X-Trace: twister.nyroc.rr.com 1041443743 66.24.101.160 (Wed, 01 Jan 2003 12:55:43 EST) NNTP-Posting-Date: Wed, 01 Jan 2003 12:55:43 EST Xref: newsfeed1.easynews.com comp.databases.oracle.tools:55590 X-Received-Date: Wed, 01 Jan 2003 11:03:49 MST (news.easynews.com) Thanks for the reply but I'm still getting a ORA-00907 missing right parenthesis at line 6. On Wed, 01 Jan 2003 00:37:55 GMT, Karsten Farrell wrote: >Steven C. Kondolf wrote: >> 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 >You have to give the inline view an alias name. You also need to give >the sum an alias. Try something like this: > >select x.custno, x.sum_sales >from (select custno, sum(sales) as sum_sales > from table > where date between 200201 and 200212 > group by custno > order by sum(sales) desc) x >where rownum<51; Peace, Steve skondolf@garbage.rochester.rr.com