Limitting rows with ROWNUM

Some months back I asked about limiting the number of rows returned in a query in Oracle SQL.

The answer is to use the ROWNUM pseudo-column.

Eg:

    select columnname
    from tablename
    where rownum <= MAXROWS

Someone mentioned a potential problem (which has just been mentioned again) regarding the fact that, if you do an "order by", Oracle will number the rows before ordering. Therefore, the query will stop at a specific quantity of returns, but there will be gaps.

The solution to this problem was also posted, and I forgot to save it. We're in such a rush, just being able to limit the rows was enough at the time. We're still in a rush, but the issue might come up. Better to be ready in advance.

So, if it's not too much of a bother, I'd be massively grateful if the solution could be re-posted.

Thanks in advance!

Alvin Sylvain
alvin_at_c-square.com
++ Spams and Junk-mail are NOT appreciated. ++ Received on Tue Jan 07 1997 - 00:00:00 CST