Those familiar with MySQL and PostgreSQL will know about the LIMIT clause these database's have. For those not in the know, the LIMIT clause will limit your resultset to a specific number, it can also start your results of at a specific offset. So for example LIMIT 10 will restrict your results to 10 records, and LIMIT 2,10 will restrict your resultset to 10, but will start the results are row 2. This is great for pagination in your applications.

Unfortunately Oracle doesn't have any such thing, and you have to use Oracles special ROWNUM column instead to limit your results. This is not as easy to use as the above, but does the same thing. You could always use ColdFusion's maxrows, but this is not wise on large recordsets as the database will have to pull back all records, only for you to show a select few of them. To use an offset in your resultset is even more complex, but thats for another article!

ROWNUM doesn't work how you would like it to either, Oracle assigns the ROWNUM value to all result columns before the ORDER BY has been complete. ROWNUM is also incremented only after it is given. This, therefore prevents a simple query like:

SELECT * FROM tableName WHERE ROWNUM <= 10

The correct way to use the ROWNUM in this situation would be:

SELECT * FROM
(
  SELECT * FROM tableName ORDER BY recordDate Desc 
)
WHERE ROWNUM <= :10

This will use Oracle's optimisers to pull back the top 10 results in the fastest way it can. To read more on how this works, have a read of this Ask Tom article. This article also goes in to depth on how to get pagination working with Oracle.