Re: query problem help needed

From: Colin Sutherland <c.sutherland_at_easynet.com>
Date: Sat, 2 May 1998 12:25:36 +0100
Message-ID: <6ievs6$sqo$1@apple.news.easynet.net>

Hi

Here another option

 SELECT        name, max(size)
    FROM         tablename
  WHERE        rownum < 21

 GROUP BY name

HTH Colin.
Nuno Guerreiro wrote in message <35489c68.779831949_at_news.telecom.pt>...
>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 Sat May 02 1998 - 06:25:36 CDT