Message-Id: <10739.126594@fatcity.com> From: Jacques Kilchoer Date: Fri, 12 Jan 2001 15:23:01 -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_01C07CEE.9B623860 Content-Type: text/plain; charset="iso-8859-1" See my comments at the end of this e-mail. First message: > > --- Arvind Aggarwal wrote: > > > select salary from emp where rownum<21 order by > > > salary desc; Second message: > --- james ellis 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@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 RE: Top-N records.

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

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

Second message:
> --- james ellis <jellis24_gso@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@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.