MySQL SELECT DISTINCT

Your table may contain duplicate values in a column, and in some scenarios, you may need to get only unique records from a table. To remove duplicate records with the SELECT statement, you can use the DISTINCT clause. Here is the syntax of the DISTINCT clause:

SELECT DISTINCT column1, column2, ... FROM tableName;

 

 

Example: MySQL SELECT DISTINCT

In the following example, we have used the DISTINCT clause with the SELECT statement to get only the unique names from our table “Persons”. This table stores Persons with their names.

> SELECT * FROM Persons;

+----------+-----------+--------+-------------------------------+
| PersonID |    Name   |  age   |             Address           |
+----------+-----------+--------+-------------------------------+
|    101   |   Alex    |   25   | 819 Saint Francis Way         |
|    102   |   Alex    |   30   | 285 Java Lane Missouri        |
|    103   |   Alex    |   45   | 188 Clay Street Indiana       |
|    104   |   Emily   |   15   | 171 Jarvisville Road Michigan |
|    105   |   Jean    |   35   | 188 Clay Street Indiana       |
|    106   |   Bob     |   40   | 285 Java Lane Missouri        |
|    107   |   Bob     |   22   | 819 Saint Francis Way         |
+----------+-----------+--------+-------------------------------+

By using the DISTINCT clause, we only get the unique names:

SELECT DISTINCT(Name) FROM Persons;

Output:

+-----------+
|    Name   |
+-----------+
|   Alex    |
|   Emily   |
|   Jean    |
|   Bob     |
+-----------+

 

 

Using “WHERE” clause with “DISTINCT”

In this example, we have used WHERE clause with SELECT/DISTINCT statement to extract only those people for whom the age is greater than or equal to 30.

SELECT DISTINCT(Name) FROM Persons WHERE Age >= 30;

Output:

+-----------+
|    Name   |
+-----------+
|   Alex    |
|   Jean    |
|   Bob     |
+-----------+

 

Using “DISTINCT” statement with aggregate functions

You can use DISTINCT clause with an aggregate function, such as SUM, AVG, and COUNT, to remove duplicate rows before the aggregate functions are applied to the results.

For example, to count people whose age is greater than or equal to 30, use the following query:

SELECT COUNT(DISTINCT Name) FROM Persons WHERE Age >= 30;

Output:

+-------------------------+
|  COUNT(DISTINCT Name)   |
+-------------------------+
|                      3  |
+-------------------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More