Summary: In this tutorial, you will learn how to use MySQL DISTINCT with SELECT statement to eliminate duplicate records in the selected result set.
Sometimes when retrieving data from database table, you get duplicate records which are not expected. In order to remove those duplicate records, you need to use DISTINCT keyword along with SELECT statement. The syntax of SQL DISTINCT is as follows:
1 |
SELECT DISTINCT columns |
Let take a look a simple example of using DISTINCT to select unique last name from employee table.
+-----------+
| lastname |
+-----------+
| Bondur |
| Bondur |
| Bott |
| Bow |
| Castillo |
| Firrelli |
| Firrelli |
| Fixter |
| Gerard |
| Hernandez |
| Jennings |
| Jones |
| Kato |
| King |
| Marsh |
| Murphy |
| Nishi |
| Patterson |
| Patterson |
| Patterson |
| Thompson |
| Tseng |
| Vanauf |
+-----------+
23 rows in set (0.00 sec)
Because in the employees table we have some employee records with the same last name so we get duplicate last names in the result set. Let apply DISTINCT keyword in the SELECT statement to remove those duplicate last names.
1 |
SELECT DISTINCT lastname |
+-----------+
| lastname |
+-----------+
| Bondur |
| Bott |
| Bow |
| Castillo |
| Firrelli |
| Fixter |
| Gerard |
| Hernandez |
| Jennings |
| Jones |
| Kato |
| King |
| Marsh |
| Murphy |
| Nishi |
| Patterson |
| Thompson |
| Tseng |
| Vanauf |
+-----------+
19 rows in set (0.00 sec)
As you can see in the new result set, four duplicate records are eliminated from the list when we used DISTINCT.
The DISTINCT keyword can be applied with more than one column. In this case, the combination of all columns are used to define the uniqueness of a record in the return result set. For example, to get all cities and states of customers in the customers table, we can use the following query:
1 |
SELECT DISTINCT city, state |
Related posts:
- Using MySQL SELECT Statement to Query Data
- Managing Database Index in MySQL
- Managing Databases in MySQL
- Working with Database Table – Part I
- Changing Table Structure Using MySQL ALTER TABLE
