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

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>

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 [<A = HREF=3D"mailto:oradba9_at_yahoo.com">mailto:oradba9_at_yahoo.com</A>] > 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.

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

10

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

<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