Re: 40 values in desc order

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.
>> > internet: hietanen%lut.fi
>>
>>
>> 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;
>
>This is probably rather SLOW. I am sure some SQL cracks will have a better
>solution.

Unfortunately, it won't even work, as you have an ORDER BY in a subquery, which is a nono.

There is no way to limit the number of rows selected, and use an ORDER BY, in the same SQL statement. This is because the ROWNUM value is assigned before the rows are ordered by the ORDER BY clause.

Note that, using a view, what you want to accomplish is not difficult at all.

Mark Received on Fri Jan 08 1993 - 22:29:42 CET