MySQL DISTINCT

Summary: In this chapter, we will learn how to eliminate duplicate rows in a result set using MySQL DISTINCT clause.

Using MySQL DISTINCT to Eliminate Duplicates

Introduction to MySQL DISTINCT clause

In a table, a column may contain many duplicate values and sometimes you only want to list the different (distinct) values. The DISTINCT keyword can be used to return only distinct (different) values. We use the DISTINCT clause in the SELECT statement.

The syntax of using the DISTINCT clause is as follows:

1 2 3 4 5 6

SELECT DISTINCT  COLUMNS FROM  table_name WHERE  where_conditions;

MySQL DISTINCT example

Let’s take a look a simple example of using the DISTINCT clause to select the unique jobtitle of employees from the employees table.

First, we will execute query the without DISTINCT from the employees table using the SELECT statement as follows:

1	SELECT jobtitle FROM employees;

Result:

Some employees have the same job title like Sales Rep, Software Engineer, Network Engineer, etc.

So for eliminate the duplicate job title, we add the DISTINCT clause to the SELECT statement as follows:

1	SELECT DISTINCT jobtitle FROM employees;

we can see in below image that duplicate job title are eliminated in the result set when we used the DISTINCT clause.

Result:

 

MySQL DISTINCT with multiple columns

You can use the DISTINCT clause with more than one column. In this case, MySQL uses the combination of all columns to determine the uniqueness of the row in the result set.

For example, to get the unique combination of jobtitle and salary from the employees table, we can use simply the following query:

1 2 3 4

SELECT DISTINCT  jobtitle, salary FROM  employees;

Result:

Note: Without the DISTINCT clause, we will get duplicate combination of jobtitle and salary.

MySQL DISTINCT and aggregate function

You can use the DISTINCT clause with an aggregate function e.g., SUM, AVG, and COUNT, to remove duplicate rows before MySQL applies the aggregate function to the result set.

For example, We will execute query to count the unique salary of employees  who is Software Engineer. WHERE clause is use for filter the data. In brief detail of WHERE clause we’ll learn in next chapter.

1 2 3 4 5 6

SELECT  COUNT(DISTINCT salary) FROM  employees WHERE  jobtitle = 'Software Engineer';

Result:

In this chapter, we have learnt various ways of using MySQL DISTINCT clause such as eliminating duplicate rows and counting values based on condition.

---

MySQL SELECT MySQL WHERE

---