Re: rownum

( Btw, cross-posting is a little rude, as is not checking deja for this question...it has been asked and answered many, many times...)

There is no 'TOP' in an Oracle data set...to get a sorted, limited result set you need to use:
( In Oracle 8.1 and above - maybe 8i)

Select * from (select * from table_name order by field_name) where rownum < 11;

[ The answers you will get using the 'select * from table where rownum <= 10 order by field_name' construct will not give you what you expect...it will give you the first 10 records returned by the select statement and THEN sort them...) Also, the rownum is not an internal index but a pseudo-variable that counts the rows returned as they come in...Not to be confused with ROWID...

"Chaz" <chaz_at_hates.spam> wrote:

>can anyone help me find a way to limit the number of records returned in a
>select statement? here's what i mean:
>
>in MS SQL Server, the "TOP n" kewords (as in "SELECT TOP 10 fname FROM
>table") specify how many records *out of the resultset generated from the
>rest of the select statement* are actually returned. in Oracle, the closest
>thing I've found is "WHERE rownum <= n". But this rownum is an internal
>index, so it doesn't limit the resultset to the top n of that resultset, but
>to records whose rownum is <= n. Theres a huge difference here, and I'm
>totally stumped.
>
>is there a similar keyword in Oracle?
>do i have to spend the next week writing a stored proc to achieve this?
>any clues?
>
>thx in advance.
>
Received on Tue Feb 06 2001 - 16:41:33 CET