Re: How to do a limited select without using a where clause?

Actually you can do it only in 8i. Oracle 7/8 require the ORDER BY clause to be related to the final result set. The statement in question will generate a syntax error. In 8i you can have as many ORDER BY's as you want and they will work as written.

Regards
Karen

TurkBear wrote:

> 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:
> Oracle8i Enterprise Edition Release 8.1.5.0.5 - Production
> With the Partitioning option
> PL/SQL Release 8.1.5.0.5 - Production
>
> 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
> -----------
> 00001018
> 00004079
> 00004417
> 00005381
> 00005648
> 00005782
> 00006969
>
> 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