Re: 40 values in desc order
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