Re: Top 20 records in a table ??

One more possible solution. This one comes from Frank Naude's www.orafaq.org list of frequently asked questions. I modified it to use the SCOTT/TIGER EMP table and return the 'top 5' salaries. SQL> select empno, ename, sal from emp a   2 where 5 >= (select count(distinct sal)

  3                from   emp b
  4                where  b.sal >= a.sal)

     EMPNO ENAME SAL
---------- ---------- ----------

      7839 KING             5000
      7788 SCOTT            3000
      7902 FORD             3000
      7566 JONES            2975
      7698 BLAKE            2850
      7782 CLARK            2450

6 rows selected.

As you can see it yields 6 employee's but the 5 highest salaries - which I think was the original question. Any solution based on ROWNUM <= n will return the FIRST n records, which will only be the "highest" or "lowest" n IF the table has been loaded with records sorted in the same way and no "out of sort order" additions have been made.

The cursor will work, but will return only n records, which may or may not be what is required (if my interpretation of the question is correct, it will not be the desired result). You could write a more complex cursor which would compare the currently fetched salary with the previously fetched salary, increment a counter each time the value changed and stop the cursor when the counter reached 20, but the above SELECT is probably easier to write. What you can do with a cursor, that I don't think any other method can do (and which MAY be what the original author is looking for) is to grab the first 19 records without qualification, then grab record 20 and any/all additional records where the salary is the same as the salary of record 20. Hope this helps,
Paul

In article <85kjng$sm7$1_at_nnrp1.deja.com>,   trumpet999_at_my-deja.com wrote:
> Nobody has suggested using a cursor yet. How about the following. Go
> easy on me I"m new to pl/sql.
>
> Mike
>
> ----- begin of source
>
> declare
> cursor cursor1 is
> select id, name, pay
> from emp order by pay desc;
>
> v_record1 type cursor1%type;
> v_counter number := 0;
>
> begin
> open cursor1;
>
> loop
> fetch cursor1 into v_record1;
>
> ... process data ....
>
> v_counter := v_counter + 1;
> exit when cursor1%notfound or v_counter = 20;
> end loop;
>
> end;
>
> ---end of source
>
> In article <1ca4f2e8.4b527e23_at_usw-ex0102-016.remarq.com>,
> agichen <agiNOagSPAM_at_feib.com.tw.invalid> wrote:
> > Hello,
> > How to select the highest 20 records from a table via sql*plus ??
> > For example,
> > emp table
> > ----------------
> > id number(5),
> > name varchar2(20),
> > pay number(8)
> >
> > how to select the highest 20 pay from emp ??
> >
> > Any idea ??
> > Rgds,
> > Agi
> >
> > * Sent from RemarQ http://www.remarq.com The Internet's Discussion
> Network *
> > The fastest and easiest way to search and participate in Usenet -
> Free!
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 13 2000 - 16:20:52 CET