HomeOracle Limiting Rows When Retrieving Table Data
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)
The DISTINCT keyword prevents duplicate (identical) rows from being included in the result set.
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.
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