Recipe1.9.Limiting the Number of Rows Returned

Recipe 1.9. Limiting the Number of Rows Returned

Problem

You want to limit the number of rows returned in your query. You are not concerned with order; any n rows will do.

Solution

Use the built-in function provided by your database to control the number of rows returned.

DB2

In DB2 use the FETCH FIRST clause:

 1 select * 2   from emp fetch first 5 rows only 

MySQL and PostgreSQL

Do the same thing in MySQL and PostgreSQL using LIMIT:

 1 select * 2   from emp limit 5 

Oracle

In Oracle, place a restriction on the number of rows returned by restricting ROWNUM in the WHERE clause:

 1 select * 2   from emp 3  where rownum <= 5 

SQL Server

Use the TOP keyword to restrict the number of rows returned:

 1 select top 5 * 2   from emp 

Discussion

Many vendors provide clauses such as FETCH FIRST and LIMIT that let you specify the number of rows to be returned from a query. Oracle is different, in that you must make use of a function called ROWNUM that returns a number for each row returned (an increasing value starting from 1).

Here is what happens when you use ROWNUM <= 5 to return the first five rows:

1. Oracle executes your query.

2. Oracle fetches the first row and calls it row number 1.

3. Have we gotten past row number 5 yet? If no, then Oracle returns the row, because it meets the criteria of being numbered less than or equal to 5. If yes, then Oracle does not return the row.

4. Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).

5. Go to step 3.

As this process shows, values from Oracle's ROWNUM are assigned after each row is fetched. This is a very important and key point. Many Oracle developers attempt to return only, say, the fifth row returned by a query by specifying ROWNUM = 5.

Using an equality condition in conjunction with ROWNUM is a bad idea. Here is what happens when you try to return, say, the fifth row using ROWNUM = 5:

1. Oracle executes your query.

2. Oracle fetches the first row and calls it row number 1.

3. Have we gotten to row number 5 yet? If no, then Oracle discards the row, because it doesn't meet the criteria. If yes, then Oracle returns the row. But the answer will never be yes!

4. Oracle fetches the next row and calls it row number 1. This is because the first row to be returned from the query must be numbered as 1.

5. Go to step 3.

Study this process closely, and you can see why the use of ROWNUM = 5 to return the fifth row fails. You can't have a fifth row if you don't first return rows one through four!

You may notice that ROWNUM = 1 does, in fact, work to return the first row, which may seem to contradict the explanation thus far. The reason ROWNUM = 1 works to return the first row is that, to determine whether or not there are any rows in the table, Oracle has to attempt to fetch at least once. Read the preceding process carefully, substituting 1 for 5, and you'll understand why it's OK to specify ROWNUM = 1 as a condition (for returning one row).