Message-Id: <10739.126589@fatcity.com> From: Jacques Kilchoer Date: Fri, 12 Jan 2001 14:34:16 -0800 Subject: RE: Top-N records. This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C07CE7.CC140E90 Content-Type: text/plain; charset="iso-8859-1" > -----Original Message----- > From: Aldi Barco [mailto:ipal@hotmail.com] > Sent: vendredi, 12. janvier 2001 13:24 > > Could you please send me the trick to select top-20 > salary of scott.employee table ? At the end of each e-mail coming from the list it says the following: << Please see the official ORACLE-L FAQ: http://www.orafaq.com >> I found the answer there. http://www.orafaq.com/faqsql.htm#TOP How does one select the TOP N rows from a table? Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example: SELECT * FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC) WHERE ROWNUM < 10; Use this workaround with prior releases: SELECT * FROM my_table a WHERE 10 >= (SELECT COUNT(DISTINCT maxcol) FROM my_table b WHERE b.maxcol >= a.maxcol) ORDER BY maxcol DESC; ------ any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer. Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com ------_=_NextPart_001_01C07CE7.CC140E90 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: Top-N records.

> -----Original Message-----
> From: Aldi Barco [mailto:ipal@hotmail.com]
> Sent: vendredi, 12. janvier 2001 13:24
>
> Could you please send me the trick to select = top-20
> salary of scott.employee table ?

At the end of each e-mail coming from the list it = says the following:

<< Please see the official ORACLE-L FAQ: http://www.orafaq.com >>

I found the answer there.

http://www.orafaq.com/faqsql.htm#TOP

How does one select the TOP N rows from a = table?
Form Oracle8i one can have an inner-query with an = ORDER BY clause. Look at this example:
        SELECT = *
        = FROM   (SELECT * FROM my_table ORDER BY col_name_1 = DESC)
        = WHERE  ROWNUM < 10;

Use this workaround with prior releases:
        SELECT = *
          FROM = my_table a
         = WHERE 10 >=3D (SELECT COUNT(DISTINCT maxcol)
          &nb= sp;           &nb= sp; FROM my_table b
          &nb= sp;            = WHERE b.maxcol >=3D a.maxcol)
         = ORDER BY maxcol DESC;

------
any ignorant comments made are the sole = responsibility of J. R. Kilchoer and should not reflect adversely upon = my employer.

 
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.