How to use the ROWNUM pseudo column to limit the number of rows - Oracle

In addition to eliminating duplicate rows, you can limit the number of rows that are retrieved by a SELECT statement. To do that, you can use the ROWNUM pseudo column as shown in figure 3-10. A pseudo column works similarly to a column in a table. However, you can only use a pseudo column to select data. In other words, you can’t insert, update, or delete the values stored in a pseudo column.

If you want to learn more about how pseudo columns work, you can search the Oracle Database SQL Reference manual for pseudocolumn. Note that the Oracle documentation doesn’t include a space between the words pseudo and column.

The first example shows how to limit the number of rows in the result set to the first five rows. Here, the ROWNUM pseudo column is used in the WHERE clause to return the first five rows in the Invoices table.

The second example shows how to add an ORDER BY clause to sort the first five rows of the table so the largest invoice total is displayed first. Since the sort operation is applied after the first five rows are retrieved, this doesn’t retrieve the five largest invoice totals in the Invoices table. Instead, it returns the first five rows of the table and then sorts them.

If you want to return the five largest invoice totals for the entire Invoices table, you need to sort the result set before you use the ROWNUM pseudo column to limit the number of rows included in the result set. To do that, you can use a subquery as shown in the third example. In the FROM clause, this example supplies a SELECT statement that sorts the Invoices table instead of supplying the name of the Invoices table. As a result, the table is sorted before the WHERE clause is applied.

For more information about working with subqueries, see chapter 6. In addition, if the ROWNUM pseudo column isn’t adequate for your needs, you might want to use the ROW_NUMBER function described in chapter 8.

A SELECT statement that uses the ROWNUM pseudo column to limit the number of rows in the result set

SELECT vendor_id, invoice_total
FROM invoices
WHERE ROWNUM <= 5

A SELECT statement that sorts the result set after the WHERE clause

SELECT vendor_id, invoice_total
FROM invoices
WHERE ROWNUM <= 5
ORDER BY invoice_total DESC

A SELECT statement that sorts the result set before the WHERE clause

SELECT vendor_id, invoice_total
FROM (SELECT * FROM invoices
ORDER BY invoice_total DESC)
WHERE ROWNUM <= 5

Description

1. You can use the ROWNUM pseudo column to limit the number of rows included in the result set.
   
2. If you want to sort the result set before you use the ROWNUM pseudo column to limit the number of rows included in the result set, you can use a subquery as shown in the third example. For more information about working with subqueries, see chapter 6.

--------------------------------------------Figure 3-10 How to use the ROWNUM pseudo column

Please check back regularly for the continuation of this series.