Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Top-N records.

RE: Top-N records.

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 12 Jan 2001 15:23:01 -0800
Message-Id: <10739.126594@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_01C07CEE.9B623860
Content-Type: text/plain;

        charset="iso-8859-1"

See my comments at the end of this e-mail.

First message:
> > --- Arvind Aggarwal <oradba9_at_yahoo.com> wrote:
> > > select salary from emp where rownum<21 order by
> > > salary desc;

Second message:
> --- james ellis <jellis24_gso_at_yahoo.com> wrote:
> > Will this solution give you the top20 salaries in
> > the table? Or will it give you the first 20 salaries
> > then order those?

Third message:
> From: Arvind Aggarwal [mailto:oradba9_at_yahoo.com]
> It will give you the top20 salaries in the table.

Actually I think that it will NOT give the top 20 salaries in the table. See the example below where I insert 7 salaries in an emp table. I used Arvind Aggarwal's query to try to retrieve the top 5 salaries. The query did NOT retrieve the top 5 salaries.

SQL> create table emp (empno number, salary number) ;

Table created.

SQL> insert into emp (empno, salary) values (1, 10) ;

1 row created.

SQL> insert into emp (empno, salary) values (2, 20) ;

1 row created.

SQL> insert into emp (empno, salary) values (3, 30) ;

1 row created.

SQL> insert into emp (empno, salary) values (4, 40) ;

1 row created.

SQL> insert into emp (empno, salary) values (5, 50) ;

1 row created.

SQL> insert into emp (empno, salary) values (6, 60) ;

1 row created.

SQL> insert into emp (empno, salary) values (7, 70) ;

1 row created.

SQL> commit ;

Commit complete.

SQL> select salary from emp
  2 where rownum < 6
  3 order by salary desc ;

    SALARY


        50
        40
        30
        20
        10



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_01C07CEE.9B623860
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>

<P><FONT SIZE=3D2>See my comments at the end of this e-mail.</FONT> </P>

<P><FONT SIZE=3D2>First message:</FONT>
<BR><FONT SIZE=3D2>&gt; &gt; --- Arvind Aggarwal = &lt;oradba9_at_yahoo.com&gt; wrote:</FONT>
<BR><FONT SIZE=3D2>&gt; &gt; &gt; select salary from emp where = rownum&lt;21 order by</FONT>
<BR><FONT SIZE=3D2>&gt; &gt; &gt; salary desc;</FONT> </P>

<P><FONT SIZE=3D2>Second message:</FONT> <BR><FONT SIZE=3D2>&gt; --- james ellis &lt;jellis24_gso_at_yahoo.com&gt; = wrote:</FONT>
<BR><FONT SIZE=3D2>&gt; &gt; Will this solution give you the top20 = salaries in</FONT>
<BR><FONT SIZE=3D2>&gt; &gt; the table? Or will it give you the first = 20 salaries</FONT>
<BR><FONT SIZE=3D2>&gt; &gt; then order those?</FONT> </P>

<P><FONT SIZE=3D2>Third message:</FONT>
<BR><FONT SIZE=3D2>&gt; From: Arvind Aggarwal [<A = HREF=3D"mailto:oradba9_at_yahoo.com">mailto:oradba9_at_yahoo.com</A>]</FONT> <BR><FONT SIZE=3D2>&gt; It will give you the top20 salaries in the = table.</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Actually I think that it will NOT give the top 20 = salaries in the table. See the example below where I insert 7 salaries = in an emp table. I used Arvind Aggarwal's query to try to retrieve the = top 5 salaries. The query did NOT retrieve the top 5 = salaries.</FONT></P>

<P><FONT SIZE=3D2>SQL&gt; create table emp (empno number, salary = number) ;</FONT>
</P>

<P><FONT SIZE=3D2>Table created.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; insert into emp (empno, salary) values (1, = 10) ;</FONT>
</P>

<P><FONT SIZE=3D2>1 row created.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; insert into emp (empno, salary) values (2, = 20) ;</FONT>
</P>

<P><FONT SIZE=3D2>1 row created.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; insert into emp (empno, salary) values (3, = 30) ;</FONT>
</P>

<P><FONT SIZE=3D2>1 row created.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; insert into emp (empno, salary) values (4, = 40) ;</FONT>
</P>

<P><FONT SIZE=3D2>1 row created.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; insert into emp (empno, salary) values (5, = 50) ;</FONT>
</P>

<P><FONT SIZE=3D2>1 row created.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; insert into emp (empno, salary) values (6, = 60) ;</FONT>
</P>

<P><FONT SIZE=3D2>1 row created.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; insert into emp (empno, salary) values (7, = 70) ;</FONT>
</P>

<P><FONT SIZE=3D2>1 row created.</FONT>
</P>

<P><FONT SIZE=3D2>SQL&gt; commit ;</FONT> </P>

<P><FONT SIZE=3D2>Commit complete.</FONT> </P>

<P><FONT SIZE=3D2>SQL&gt; select salary from emp</FONT>
<BR><FONT SIZE=3D2>&nbsp; 2&nbsp; where rownum &lt; 6</FONT>
<BR><FONT SIZE=3D2>&nbsp; 3&nbsp; order by salary desc ;</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; SALARY</FONT>
<BR><FONT SIZE=3D2>----------</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 40</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 20</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 10</FONT> </P>

<P><FONT SIZE=3D2>------</FONT>
<BR><FONT SIZE=3D2>any ignorant comments made are the sole = responsibility of J. R. Kilchoer and should not reflect adversely upon = my employer.</FONT></P>

<P><FONT SIZE=3D2>&nbsp;</FONT>
<BR><FONT SIZE=3D2>Jacques R. Kilchoer</FONT>
<BR><FONT SIZE=3D2>(949) 754-8816</FONT>
<BR><FONT SIZE=3D2>Quest Software, Inc.</FONT>
<BR><FONT SIZE=3D2>8001 Irvine Center Drive</FONT>
<BR><FONT SIZE=3D2>Irvine, California 92618</FONT>
<BR><FONT SIZE=3D2>U.S.A.</FONT>
Received on Fri Jan 12 2001 - 17:23:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US