RE: Top-N records.

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 12 Jan 2001 14:34:16 -0800
Message-Id: <10739.126589@fatcity.com>

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_at_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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2652.35">
<TITLE>RE: Top-N records.</TITLE>
</HEAD>
<BODY>

> -----Original Message-----
 > From: Aldi Barco [<A =
HREF=3D"mailto:ipal_at_hotmail.com">mailto:ipal_at_hotmail.com</A>]
 > 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: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A> >>


I found the answer there.


<A HREF=3D"http://www.orafaq.com/faqsql.htm#TOP" =
TARGET=3D"_blank">http://www.orafaq.com/faqsql.htm#TOP</A>


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
 <FONT =

SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
WHERE b.maxcol &gt;=3D a.maxcol)</FONT>

          =
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.
 <A HREF=3D"http://www.quest.com" =
Received on Fri Jan 12 2001 - 16:34:16 CST