Thanks for all of the input. I tried using RANK(), however I have been informed that we are using a third-party OBDC connector (for Peregrine's Asset Center). Errrr...
-- SCOTT VAN VLIET SENIOR ANALYST SBC SERVICES, INC Tel: 858.886.3878 Fax: 858.653.6763 Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -----Original Message----- From: Steven Monaghan [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 28, 2002 11:41 AM To: CF-Talk Subject: RE: OT: Oracle version of TOP What version are you on? In 8.1 (8i) and above, you can do the following: SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM Emp ORDER BY NVL(Sal, 0) DESC) WHERE ROWNUM < 6; For 8.0 and below, you cannot do an order by in a nested select, but you can cheat by doing the following: SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM Emp union SELECT 1,2,3,4,5,6 from dual where 1=2) WHERE ROWNUM < 6; The union forces a sort (in ascending order only!) and tricks oracle into sorting. Here's a link that should help... http://www.jlcomp.demon.co.uk/faq/top_sql.html Steve ------------------------------------- Steven Monaghan Oracle DBA / Cold Fusion Developer MSC Industrial Direct Co., Inc. http://www.mscdirect.com ------------------------------------- -----Original Message----- From: Zac Spitzer [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 28, 2002 2:31 PM To: CF-Talk Subject: Re: OT: Oracle version of TOP VAN VLIET, SCOTT E (SBCSI) wrote: >Sorry for the OT, but do any of you know the Oracle function for selecting >the top n records. (ie. SELECT TOP 10 * FROM TABLE - in SQL). > >Thanks! > >Scott > rowcount is similar but my oracle skills have bit rotted :-( i think :-) z ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
