Limiting Rows When Retrieving Table Data

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