| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to do a limited select without using a where clause?
Actually you can....as far as Oracle is concerned the 'Order by MyOrder' statement IS the last component in the query ( the query in this case is a subquery ) ....It will work as written...
:--------------------------------------------------------------------------------------Connected to:
johnmas:oracle SQL> select * from ( select empl_nbr from 2 hrdata where empl_stat_cd = 'A' order by empl_nbr) 3 where rownum < 8;
EMPL_NBR
7 rows selected.
:-----------------------------------------------------------------------------------
There are over 5000 records in the source table but this returned only 7 in low to high order....
Good luck...
cjaouich_at_my-deja.com wrote:
>Thanks for the response, but I can only use the order by in the last
>component of the query (as per Ora doc)... Any other ideas? Thanks
>
>-Cyril
>------------------------------------
>Opérations téléphonie IP - Vidéotron
>Vidéotron - IP Telephony Operations
>------------------------------------
>
>In article <F4155D9F9AA4D111B9990020AFBA52D53E0D50_at_class06.ip061.sfi-
>software.com>,
> "Mark" <markag_at_wonderstats.com> wrote:
>> Do something like this:
>>
>> SELECT * FROM
>> (SELECT * FROM YOUR_TABLE
>> WHERE MYCOL = MYCRITERIA
>> ORDER BY MYORDER)
>> WHERE ROWNUM <= 20;
>>
>> The inner select returns your data set, the outer select limits the
number
>> of rows.
>>
>> <cjaouich_at_my-deja.com> wrote in message news:8mmuo2
$e5c$1_at_nnrp1.deja.com...
>> > Hi, if I am not posting to the right forum, please point me in the
>> > right direction.
>> >
>> > I am trying to do a SQL select that is using an order by clause. The
>> > statement return 100+ rows, but I only want the first 5 rows of the
>> > order by clause without specifying a limit or a where clause. Is
this
>> > possible?
>> >
>> > ie:
>> >
>> > select date, mac, fqdn, dhcp_ip from clients where mac like =
'0000%'
>> > order by date;
>> >
>> > this return me a list of 100+ rows, but I want only the last 5, and
I
>> > cannot use the date as a limit.
>> >
>> > Thanks in advance
>> >
>> > Cyril Jaouich
>> > ------------------------------------
>> > Opérations téléphonie IP - Vidéotron
>> > Vidéotron - IP Telephony Operations
>> > ------------------------------------
>> >
>> >
>> > Sent via Deja.com http://www.deja.com/
>> > Before you buy.
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Tue Aug 08 2000 - 00:00:00 CDT
 |
 |