From: johng@mm.com (TurkBear) Subject: Re: How to do a limited select without using a where clause? Date: 2000/08/08 Message-ID: <39902351.6635661@news>#1/1 Content-Transfer-Encoding: 8bit References: <8mmuo2$e5c$1@nnrp1.deja.com> <8mp4of$pv$1@nnrp1.deja.com> Content-Type: text/plain; charset=ISO-8859-1 X-Complaints-To: usenet@news.state.mn.us X-Trace: news.state.mn.us 965748020 15350 156.98.4.11 (8 Aug 2000 15:20:20 GMT) Organization: State of Minnesota Intertech Mime-Version: 1.0 Reply-To: See Message body for real address NNTP-Posting-Date: 8 Aug 2000 15:20:20 GMT Newsgroups: comp.databases.oracle.server 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 software.com>, > "Mark" 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. >> >> 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.