Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from MySQL Cookbook

Using DISTINCT to Eliminate Duplicates

Problem

You want to know which values are present in a set of values, without listing duplicate values a bunch of times. Or you want to know how many distinct values there are.

Solution

Use DISTINCT to select unique values, or COUNT(DISTINCT) to count them.

Discussion

A summary operation that doesn’t use aggregate functions is to determine which values or rows are contained in a dataset by eliminating duplicates. Do this with DISTINCT (or DISTINCTROW, which is synonymous). DISTINCT is useful for boiling down a query result, and often is combined with ORDER BY to place the values in more meaningful order. For example, if you want to know the names of the drivers listed in the driver_log table, use the following query:

mysql> SELECT DISTINCT name FROM driver_log ORDER BY name;
+-------+
| name  |
+-------+
| Ben   |
| Henry |
| Suzi  |
+-------+

A query without DISTINCT produces the same names, but is not nearly as easy to understand:

mysql> SELECT name FROM driver_log;
+-------+
| name  |
+-------+
| Ben   |
| Suzi  |
| Henry |
| Henry |
| Ben   |
| Henry |
| Suzi  |
| Henry |
| Ben   |
| Henry |
+-------+

If you want to know how many different drivers there are, use COUNT(DISTINCT):

mysql> SELECT COUNT(DISTINCT name) FROM driver_log;
+----------------------+
| COUNT(DISTINCT name) |
+----------------------+
|                    3 |
+----------------------+

COUNT(DISTINCT) ignores NULL values. If you also want to count NULL as one of the values in the set if it’s present, do this: ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata