This chapter is from the book
This chapter is from the book
This chapter is from the book
Retrieving Distinct Rows
As you have seen, SELECT returns all matched rows. But what if you did not want every occurrence of every value? For example, suppose you wanted the vendor ID of all vendors with products in your products table:
Input
SELECT vend_id FROM products;
Output
+---------+ | vend_id | +---------+ | 1001 | | 1001 | | 1001 | | 1002 | | 1002 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1003 | | 1005 | | 1005 | +---------+
The SELECT statement returned 14 rows (even though only 4 vendors are in that list) because 14 products are listed in the products table. So how could you retrieve a list of distinct values?
The solution is to use the DISTINCT keyword which, as its name implies, instructs Oracle to only return distinct values:
Input
SELECT DISTINCT vend_id FROM products;
Analysis
SELECT DISTINCT vend_id tells Oracle to only return distinct (unique) vend_id rows, and so only 4 rows are returned, as shown in the following output. If you use it, you must place the DISTINCT keyword directly in front of the column names:
Output
+---------+ | vend_id | +---------+ | 1001 | | 1002 | | 1003 | | 1005 | +---------+