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
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.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
