Limiting Rows When Retrieving Table Data

In this fifth part of a nine-part article series on retrieving data from Oracle databases with the SELECt statement, you’ll learn how to use the DISTINCT keyword and ROWNUM pseudo column to limit the number of rows returned. This article is excerpted from chapter three of the book Murach’s Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

How to use the DISTINCT keyword to eliminate duplicate rows

By default, all of the rows in the base table that satisfy the search condition in the WHERE clause are included in the result set. In some cases, though, that means that the result set will contain duplicate rows, or rows whose column values are identical. If that’s not what you want, you can include the DISTINCT keyword in the SELECT clause to eliminate the duplicate rows.

Figure 3-9 illustrates how this works. Here, both SELECT statements retrieve the vendor_city and vendor_state columns from the Vendors table. The first statement, however, doesn’t include the DISTINCT keyword. Because of that, the same city and state can appear in the result set multiple times. In the results shown in this figure, for example, you can see that “Anaheim CA” occurs twice. In contrast, the second statement includes the DISTINCT keyword, so each city/state combination is included only once.

A SELECT statement that returns all rows

SELECT vendor_city, vendor_state
FROM vendors
ORDER BY vendor_city


(122 rows selected)

A SELECT statement that eliminates duplicate rows

SELECT DISTINCT vendor_city, vendor_state
FROM vendors
ORDER BY vendor_city


(53 rows selected)

Description

  1. The DISTINCT keyword prevents duplicate (identical) rows from being included in the result set.
  2. The ALL keyword causes all rows matching the search condition to be included in the result set, regardless of whether rows are duplicated. Since this is the default, it’s a common practice to omit the ALL keyword.
  3. To use the DISTINCT or ALL keyword, code it immediately after the SELECT keyword.

——————————————–Figure 3-9 How to use the DISTINCT keyword to eliminate duplicate rows

{mospagebreak title=How to use the ROWNUM pseudo column to limit the number of rows}

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.

Google+ Comments

Google+ Comments