Re: Top 20 records in a table ??

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

As others have said, it depends whether you allow records with duplicate salaries.
But for my tuppence-worth, how's about (tested on traditional EMP table):

 select * from emp e
 where
20 > (select count(*) from emp m where m.sal > e.sal )

which means select a record as long as the count of people with a higher salary is less than twenty.

Again, select a record only if there are less than twenty records with a higher salary than THIS record. All the joys of correlated subqueries too... :-)

Or something like this...

HTH Mungo

--
Mungo Henning - it's a daft name but it goes with the face...
mungoh_at_itacs.strath.ac.uk.http://www.itacs.strath.ac.uk/
I speak for me, not my employer.