Re: Use ROWNUM with GROUP BY

From: Tom Williamson <tom.williamson_at_home.com>
Date: Sat, 20 Nov 1999 14:03:09 GMT
Message-ID: <xSxZ3.22594$HU2.51368@news.rdc1.az.home.com>

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_at_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
>
Received on Sat Nov 20 1999 - 08:03:09 CST

Re: Use ROWNUM with GROUP BY - ARRGH!