Re: Top 20 records in a table ??

None of the answers given above actually solve the problem as they only return 20 rows. It is possible that 2 or more employees have the same salary at position 20. Thus the business question may return more than 20 rows.

You then have to start to consider do you want 20 people or do you want all people in the top 20 salary positions (i.e. 2 people may have the top salary, thus is the next person number 2 or number 3 - horse racing principles make him number 3).

I have found the following bit of SQL but have not tried it myself.

Good luck.

Ronan.

code snippet follows, let us know how it works.

SELECT name

    ,id
FROM emp e1
WHERE (TO_CHAR(e1.id, ‘00000’)||

     TO_CHAR(20, ‘00000’)) >=
    (SELECT TO_CHAR(MIN(e2.pay), ‘00000’)||

          TO_CHAR(COUNT(DISTINCT e2.pay), ‘00000’)
       FROM emp e2
       WHERE e2.pay >= e1.pay)
ORDER BY e1.pay DESC
      ,e1.name;

agichen 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!
Received on Tue Jan 11 2000 - 15:24:33 CET