The DISTINCT keyword

In this section of the online MySQL guide, we will look at how to select and display records from MySQL tables using the DISTINCT keyword that eliminates the occurences of the same data.

To list all titles in our company database, we can throw a statement as:

select title from employee_data;

+----------------------------+
| title                      |
+----------------------------+
| CEO                        |
| Senior Programmer          |
| Senior Programmer          |
| Web Designer               |
| Web Designer               |
| Programmer                 |
| Programmer                 |
| Programmer                 |
| Programmer                 |
| Multimedia Programmer      |
| Multimedia Programmer      |
| Multimedia Programmer      |
| Senior Web Designer        |
| System Administrator       |
| System Administrator       |
| Senior Marketing Executive |
| Marketing Executive        |
| Marketing Executive        |
| Marketing Executive        |
| Customer Service Manager   |
| Finance Manager            |
+----------------------------+
21 rows in set (0.00 sec)

You’ll notice that the display contains multiple occurences of certain data. The SQL DISTINCT clause lists only unique data. Here is how you use it.

select DISTINCT title from employee_data;

+----------------------------+
| title                      |
+----------------------------+
| CEO                        |
| Customer Service Manager   |
| Finance Manager            |
| Marketing Executive        |
| Multimedia Programmer      |
| Programmer                 |
| Senior Marketing Executive |
| Senior Programmer          |
| Senior Web Designer        |
| System Administrator       |
| Web Designer               |
+----------------------------+
11 rows in set (0.00 sec)

This shows we have 11 unique titles in the company.

Also, you can sort the unique entries using ORDER BY.

select DISTINCT age from employee_data
ORDER BY age; 

+------+
| age  |
+------+
|   25 |
|   26 |
|   27 |
|   28 |
|   30 |
|   31 |
|   32 |
|   33 |
|   34 |
|   35 |
|   36 |
|   43 |
+------+
12 rows in set (0.00 sec)

DISTINCT is often used with the COUNT aggregate function, which we’ll meet in later sessions.