How to Retrieve Data from a Single Table - How to Eliminate Duplicate Rows
(Page 8 of 17 )
By default, all of the rows in the base table that satisfy the search condition you specify 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 8 illustrates how this works. Here, both SELECT statements retrieve the VendorCity and VendorState 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 and Boston MA occurs three times. In contrast, the second statement includes the DISTINCT keyword, so each city/state combination is included only once.
Figure 8: A SELECT statement that returns all rows
SELECT VendorCity, VendorState
FROM Vendors
ORDER BY VendorCity
(121 rows)
A SELECT statement that eliminates duplicate rows:
SELECT DISTINCT VendorCity, VendorState
FROM Vendors
(53 rows)
Description
- The DISTINCT keyword prevents duplicate (identical) rows from being included in the result set. It also causes the result set to be sorted by its first column.
- 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, you'll usually omit it.
- To use the DISTINCT or ALL keyword, code it immediately after the SELECT keyword as shown above.
This is from chapter three of Murach's SQL for SQL Server by Syverson (Murach, ISBN 1-890774-16-2, 2002). Grab a copy at your favorite book store today! Buy this book now. |
Next: How to Return a Subset of Selected Rows >>
More MS SQL Server Articles
More By Bryan Syverson
