Re: Use ROWNUM with GROUP BY - ARRGH!

Hi, All!

You can't use order by in subquery, but You can use distinct, that order the result set:

 SELECT cnt, table_name FROM
 (
  select distinct cnt*(-1), cnt, table_name, rownum   from
   (
    SELECT COUNT(*) cnt, table_name
    FROM user_tab_columns GROUP BY table_name     )
 )
 WHERE ROWNUM < 5;

Best regards,
Ivan

Tom Williamson wrote:

> 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 Mon Nov 22 1999 - 08:38:46 CST