Xref: alice comp.databases.oracle.server:74943 Path: alice!news-feed.fnsi.net!netnews.com!news.voicenet.com!nntp.giganews.com!newshub1.home.com!news.home.com!news.rdc1.az.home.com.POSTED!not-for-mail From: "Tom Williamson" Newsgroups: comp.databases.oracle.server References: <38364A21.6BA5BD5@ntsource.com> Subject: Re: Use ROWNUM with GROUP BY - ARRGH! Lines: 86 X-Newsreader: Microsoft Outlook Express 4.72.3612.1700 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3612.1700 Message-ID: Date: Sat, 20 Nov 1999 14:03:09 GMT X-Complaints-To: abuse@home.net X-Trace: news.rdc1.az.home.com 943106589 24.8.71.106 (Sat, 20 Nov 1999 06:03:09 PST) NNTP-Posting-Date: Sat, 20 Nov 1999 06:03:09 PST Organization: @Home Network When I execute your query I get : SQLWKS> SELECT * FROM ( 2> SELECT COUNT(*), table_name FROM user_tab_columns GROUP BY table_name order 3> by count(*) desc 4> ) 5> WHERE ROWNUM < 5; SELECT COUNT(*), table_name FROM user_tab_columns GROUP BY table_name order * ORA-00907: missing right parenthesis The "missing right parenthesis" occurs at the ORDER clause of the subquery. (Note: I also tried the same code as a view.) Does this really work on your system? What version of Oracle are you running? I'm running 8.0.6. Thanks - Tom Frank Hubeny wrote in message <38364A21.6BA5BD5@ntsource.com>... >I tried a similar query on user_tab_columns with a rownum of 5 to conserve >space: > >SQL> SELECT * FROM ( > 2 SELECT COUNT(*), table_name FROM user_tab_columns GROUP BY table_name order >by count(*) desc > 3 ) > 4 WHERE ROWNUM < 5; > > COUNT(*) TABLE_NAME >--------- ------------------------------ > 92 V_192216243_F_5_E_8_8_1 > 89 V_192216243_F_5_E_10_8_1 > 89 V_192216243_F_5_E_9_8_1 > 86 V_192216243_F_5_E_12_8_1 > >4 rows selected. > >I wonder if the second select could not be seen as a "view" rather than a >"subquery". > >Frank Hubeny >WExcel, Chicago (http://www.wexcel.com) > >Tom Williamson wrote: > >> Help.... >> >> I am trying to use ROWNUM to return the first 20 records of an ordered list. >> I am using a subquery to group records and return a COUNT(*) which I then >> want to see the top 20 of. For example: >> >> SELECT COUNT(*), ARTIST, TITLE FROM RECORDS_SOLD GROUP BY ARTIST,TITLE SORT >> BY COUNT(*) DESC >> >> would return a count of all records sorted from most to least popular. >> >> I have had to resort to using a subquery to return the records in order to >> use ROWNUM, otherwise each record in the GROUP BY gets it's own ROWNUM and >> of course all records return a count of 1. For example: >> >> SELECT * FROM ( >> SELECT COUNT(*), ARTIST, TITLE FROM RECORDS_SOLD GROUP BY ARTIST,TITLE >> ) >> WHERE ROWNUM < 21 SORT BY COUNT(*) DESC >> >> Unfortunately, you can't use ORDER BY in a subquery, so instead of getting >> the top 20 I get the first 20 records returned by Oracle in "whatever" >> order. Pretty useless. >> >> So, apparently I can either have the whole thing (sorted) or a random 20 >> records. >> >> Does anybody know a way to return the top "X" records from a sorted >> list??????? >> >> Thanks - Tom >