Removing Duplicate Rows
Problem
Output from a query contains duplicate rows. You want to eliminate them.
Solution
Use
DISTINCT
.
Discussion
Some queries produce results containing duplicate rows. For
example, to see who sent mail, you could query the mail
table like this:
mysql>SELECT srcuser FROM mail;
+---------+
| srcuser |
+---------+
| barb |
| tricia |
| phil |
| barb |
| gene |
| phil |
| barb |
| tricia |
| gene |
| phil |
| gene |
| gene |
| gene |
| phil |
| phil |
| gene |
+---------+
That result is heavily redundant. Adding DISTINCT
to the query removes the duplicate
rows, producing a set of unique values:
mysql>SELECT DISTINCT srcuser FROM mail;
+---------+
| srcuser |
+---------+
| barb |
| tricia |
| phil |
| gene |
+---------+
DISTINCT
works with
multiple-column output, too. The following query shows which dates are
represented in the mail
table:
mysql>SELECT DISTINCT YEAR(t), MONTH(t), DAYOFMONTH(t) FROM mail;
+---------+----------+---------------+
| YEAR(t) | MONTH(t) | DAYOFMONTH(t) |
+---------+----------+---------------+
| 2006 | 5 | 11 |
| 2006 | 5 | 12 |
| 2006 | 5 | 13 |
| 2006 | 5 | 14 |
| 2006 | 5 | 15 |
| 2006 | 5 | 16 |
| 2006 | 5 | 17 |
| 2006 | 5 | 19 |
+---------+----------+---------------+
To count the number of unique values in a column, use COUNT(DISTINCT)
:
mysql>SELECT COUNT(DISTINCT srcuser) FROM mail;
+-------------------------+
| COUNT(DISTINCT srcuser) |
+-------------------------+
| 4 |
+-------------------------+
See Also
Chapter 8 revisits DISTINCT
and ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.