Re: query problem help needed

On Thu, 30 Apr 1998 16:06:45 +0100, Alistair Thomson <alistair.thomson_at_spinxcst.co.uk> wrote:

>hi,

>student needs help

>how can i select the 20 biggest files from my table????????

>table files
>-------------
>naam varchar2(50)
>size integer

>thanks

>bart

>Try
>
>select name,size from tablename where rownum <21 order by size desc;
>
>Alistair
>
>
>

Nop, the above query will select 20 rows from the table (by the order Oracle wishes to give you, not exactly the order by which the rows were inserted on the table) and THEN it will order those 20 rows by size - this will not give you the 20 highest sizes from the table.

I've tried the following query in SQL*Plus and it worked:

select a.naam, a.size
from files a, files b
where a.size <= b.size
group by a.naam, a.size
having count(*) <= 20;

Hope this helps,

Nuno Guerreiro Received on Thu Apr 30 1998 - 10:54:15 CDT