Re: 40 values in desc order

From: Michael Friedman <mfriedma_at_uucp>
Date: 9 Jan 93 17:13:38 GMT
Message-ID: <1993Jan9.171338.27327_at_oracle.us.oracle.com>


In article <1993Jan8.200238.534_at_osnbe.Olivetti.ch> rheiger_at_renext.eiger.olivetti.ch writes:
>In article <6622.2b485a61_at_hayes.com> fgreene_at_hayes.com writes:
>> In article <Bzo2t0.67_at_lut.fi>, hietanen_at_lut.fi (Pentti Hietanen) writes:
 

>> > What kind of sql sentence should we use to get 40 values
>> > from database in descending order?
 

>> > Pentti Hietanen, student of Lappeenranta University of Technology, Finland.
 

>> Probably the easiest way is to use the ROWNUM function. For example,
 

>> SELECT field1, field2, fieldn
>> FROM the_table
>> WHERE ROWNUM < 40;
 

>This will only limit the output to the first 40 rows. It will not order them in
>a predictible manner. If you want to order the rows descending you should
>probably use the "ORDER BY attr1, attr2... DESCENDING" clause. However this
>will not restrict the select to 40 rows. To do both you could
 

>select <attribute1>,....
>from tablename
>where key_attr_list in (
>select key_attr_list
>from tablename
>order by order_attr_list descending)
>where rownum < 40;

Sorry, but this will not work either.

To start with, you can't have an order by in a subquery.

Even if you could, since you are looking at the rownum in the outer query this would be no different than

select <attribute1>,...
from tablename
where rownum < 40

Finally, there is no order by in the outer query.

If the questioner just wants any 40 rows in descending order then

select <attribute1>,...
from tablename
where rownum <= 40
order by key desc

will work. If the questioner wants the top forty then...

select <attribute1>,...
from tablename t1
where 40 <= (select count(*) from tablename t2 where t2.key >= t1.key) order by key desc

-- 
-------------------------------------------------------------------------------
I am not an official Oracle spokesman.  I speak for myself and no one else.
Received on Sat Jan 09 1993 - 18:13:38 CET

Original text of this message