Removing Duplicate Rows
Problem
Output from a query contains duplicate records. You want to eliminate them.
Solution
Use DISTINCT
.
Discussion
Some queries produce results containing duplicate records. 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 |
+---------+
But that result is heavily redundant. Adding
DISTINCT
to the query removes the duplicate
records, 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) |
+---------+----------+---------------+
| 2001 | 5 | 11 |
| 2001 | 5 | 12 |
| 2001 | 5 | 13 |
| 2001 | 5 | 14 |
| 2001 | 5 | 15 |
| 2001 | 5 | 16 |
| 2001 | 5 | 17 |
| 2001 | 5 | 19 |
+---------+----------+---------------+
To count the number of unique values, do this:
mysql> SELECT COUNT(DISTINCT srcuser) FROM mail;
+-------------------------+
| COUNT(DISTINCT srcuser) |
+-------------------------+
| 4 |
+-------------------------+
COUNT(DISTINCT)
requires MySQL 3.23.2 or higher. ...
Get MySQL Cookbook 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.