Re: Top 20 records in a table ??

The answer of Lindsay Walker is false

The sort is processed after the break.

With 8i, you can write :

select * from
(SELECT pay
FROM emp
order by pay desc)
[Quoted] WHERE rownum < 21

But the 2d solution is better :
Create an Index on pay column:

Create index i_pay on emp(pay);

Then request the table enforcing use of this index :

Select pay from emp
where pay >0

Although all the pay are greater than 0, this is to force the use of index

And then read the index in the other side :

Select /*+ index_desc (emp i_pay) */ pay from emp where pay >0
And Rownum<21;

This is the faster solution. Only good lines are read.

Have a good day !!!

agichen <agiNOagSPAM_at_feib.com.tw.invalid> a écrit dans le message : 1ca4f2e8.4b527e23_at_usw-ex0102-016.remarq.com...
> 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 Mon Jan 10 2000 - 16:59:22 CET