Oracle Equivalent to SQL Server TOP function

[Auld Nick II]

Does anyone know if there is an equivalent to SQL Servers top

(select top 5 from tblAudits where somevalue is not null)

in Oracle 9i? ?


Many Thanks,

AuldNick

[BruceG]

You can use ROWNUM in Oracle. I just did a test and it would appear that you should apply this to a resultset that is already sorted the way you want (unless you don't care and will take ANY 5 records), as opposed to the table directly (someone may correct me on this).

But the following will surely work (note that a table EXPRESSION is used in the FROM clause):
SELECT T.*
FROM (select * from tblAudits where somevalue is not null order by somefield desc) T
WHERE ROWNUM < 6

If you just do the following, I don't think you'll get the expected results:
SELECT * from tblAudits
where somevalue is not null
and ROWNUM < 6
order by somefield desc