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 there are only 4 vendors in that list) because there are 14 products 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 MySQL to only return distinct values.

• Input

SELECT DISTINCT vend_id FROM products;

• Analysis

SELECT DISTINCT vend_id tells MySQL to only return distinct (unique) vend_id rows, and so only 4 rows are returned, as seen in the following output. If used, the DISTINCT keyword must be placed directly in front of the column names.

• Output

+---------+ | vend_id | +---------+ |    1001 | |    1002 | |    1003 | |    1005 | +---------+

Caution

Can't Be Partially DISTINCT The DISTINCT keyword applies to all columns, not just the one it precedes. If you were to specify SELECT DISTINCT vend_id, prod_price, all rows would be retrieved unless both of the specified columns were distinct.