Re: Use ROWNUM with GROUP BY - ARRGH!

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 - 01:13:37 CST