From: Karen <karen.abgarian@fmr.com>
Subject: Re: How to do a limited select without using a where clause?
Date: 2000/08/08
Message-ID: <399047AD.DF39B869@fmr.com>#1/1
Content-Transfer-Encoding: 8bit
References: <8mmuo2$e5c$1@nnrp1.deja.com> <F4155D9F9AA4D111B9990020AFBA52D53E0D50@class06.ip061.sfi-software.com> <8mp4of$pv$1@nnrp1.deja.com> <39902351.6635661@news>
X-Accept-Language: en
Content-Type: text/plain; charset=iso-8859-1
X-Complaints-To: usenet@mirkwood.fmr.com
X-Trace: news-srv1.fmr.com 965756857 155.199.81.229 (Tue, 08 Aug 2000 13:47:37 EDT)
Organization: Fidelity Investments
MIME-Version: 1.0
NNTP-Posting-Date: Tue, 08 Aug 2000 13:47:37 EDT
Newsgroups: comp.databases.oracle.server


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@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@class06.ip061.sfi-
> >software.com>,
> >  "Mark" <markag@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@my-deja.com> wrote in message news:8mmuo2
 $e5c$1@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.