Re: query problem : urgent!

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Mon, 04 May 1998 20:36:35 GMT
Message-ID: <354e26d0.9290304@www.sigov.si>

On Thu, 30 Apr 1998 16:41:43 +0200, "Koen Serry & Bart Van Daele" <kaho_at_planetinternet.be> wrote:

>
>hi,
>
>student needs help
>how can i select the 20 biggest files from my table????????
>
>table files
>-------------
>naam varchar2(50)
>size integer

Here are some of the possible SQL sollutions:

Sollution 1. (*very slow* on large tables):

---

SELECT naam, size FROM files a
WHERE 20 >= (SELECT COUNT(size) FROM files b

             WHERE b.size >= a.size)
ORDER BY a.size DESC;

Sollution 2. (much faster than previous one)

---

SELECT a.naam, a.size FROM files a,
  (SELECT rowid x, -1*size y FROM files GROUP BY -1*size, rowid) b WHERE a.rowid = b.x
AND rownum <= 10;

Sollution 3. (the fastest one)

---

SELECT a.naam, a.size FROM files a , dual WHERE -1*a.size = DECODE(dual.dummy(+),'X',NULL,NULL) AND rownum <= 10;

>thanks
>
>bart

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon May 04 1998 - 15:36:35 CDT