Re: Tricky SQL question

In article <3690948F.CFA3528A_at_ogica.com>, Iain Shanks <shanksi_at_ogica.com> wrote:
>Vagelis Rados wrote:
>
>> change the sql statement to:
>> select distinct (keyword), count(keyword)
>> from keyword k
>> where rownum < 11
>> group by keyword
>> order by 2 desc;
>> The Usage of the pseudo column ROWNUM eliminates the output
>> of the wuery to 10 rows.
>> Regards,
>> -----------------------------
>> Vagelis Rados
>> Singular Computer Applications
>> mail to : vrados_at_singular.gr
>> Web Site : http://www.singular.gr
>
>This won't necessarily work. IIRC, Oracle assigns ROWNUM to each row
>as it is retrieved, before the rows are sorted by the ORDER BY so that
>the
>above will give the first 10 rows retrieved but not necessarily the 10
>with the
>largest count.

This is true. As a quick and dirty hack, try:

select * from (

         select distinct (keyword) keyword, count(keyword) keycount
         from keyword k
         group by keyword) q1 where rownum < 11 order by 2 desc;

Regards,
-Rodney Received on Wed Jan 06 1999 - 08:37:43 CST