MySQL Distinct Values Query

In most databases, you will often encounter duplicate values—except for unique primary keys. For example, a database can contain a film table with similar ratings, year of release, and other similar values.

Hence, to get a list of unique values, such as the various types of movie ratings, we need to get only the unique values using the MySQL distinct keyword.

This tutorial will go over how to use the distinct keyword in MySQL queries to get unique results.

Before we begin, we assume you have MySQL installed on your system and can perform operations on the databases.

If you need a sample database to work with, consider the Sakila database in the resource provided below:

https://dev.mysql.com/doc/index-other.html

Basic Usage

The general syntax for the MySQL DISTINCT clause is:

SELECT DISTINCT column_list FROM table_name;

Here, the column_list is the set of columns that you wish to retrieve separated by commas. The table name is the table from which to select the said values.

Example Use Case

Let us now illustrate how to use MySQL DISTINCT using an example. We are using the Sakila sample database for illustration.

In the Sakila database, you will find the actors’ table, which contains fields as shown in the command below:

DESC sakila.actor;

The output describing the table fields are shown below:

mysql> DESC sakila.actor;
+-------------+-------------------+------+-----+
| Field       | Type              | Null | Key |
+-------------+-------------------+------+-----+
| actor_id    | smallint unsigned | NO   | PRI |
| first_name  | varchar(45)       | NO   |     |
| last_name   | varchar(45)       | NO   | MUL |
| last_update | timestamp         | NO   |     |
+-------------+-------------------+------+-----+

NOTE: I’ve truncated this table to show only relevant information.

If we select the values in the actors’ table and order by the first name, the chances are high that we will have duplicate values, as shown in the query below:

SELECT first_name, last_name FROM sakila.actor ORDER BY first_name LIMIT 10;

We can see from the output that there are duplicate first names as shown below:

NOTE: We limit the output to the 10 first values as the table contains massive information. Feel free to remove the limit and see how many values there are.

+------------+-------------+
| first_name | last_name   |
+------------+-------------+
| ADAM       | HOPPER      |
| ADAM       | GRANT       |
| AL         | GARLAND     |
| ALAN       | DREYFUSS    |
| ALBERT     | NOLTE       |
| ALBERT     | JOHANSSON   |
| ALEC       | WAYNE       |
| ANGELA     | WITHERSPOON |
| ANGELA     | HUDSON      |
| ANGELINA   | ASTAIRE     |
+------------+-------------+
10 rows in set (0.00 sec)

Using the DISTINCT clause in MySQL, we can filter to get unique first names from the same table as shown in the query below:

SELECT DISTINCT first_name FROM sakila.actor ORDER BY first_name LIMIT 10;

Once we execute the query above, we will get a list of unique first names.

mysql> SELECT DISTINCT first_name FROM sakila.actor ORDER BY first_name LIMIT 10;
+------------+
| first_name |
+------------+
| ADAM       |
| AL         |
| ALAN       |
| ALBERT     |
| ALEC       |
| ANGELA     |
| ANGELINA   |
| ANNE       |
| AUDREY     |
| BELA       |
+------------+
10 rows in set (0.00 sec)

Example Use Case: Aggregate Functions

You can also use DISTINCT inside a MySQL aggregate function such as COUNT and SUM. For example, to use along with COUNT from the above query, we can do:

SELECT COUNT(DISTINCT first_name) FROM sakila.actor WHERE last_name="BERRY";
+----------------------------+
| COUNT(DISTINCT first_name) |
+----------------------------+
|                          3 |
+----------------------------+
1 row in set (0.00 sec)

The above query gives us the number of names of the distinct first names where the last_name is BERRY.

NOTE: It is good to keep in mind that even NULL values are considered duplicates by the DISTINCT clause. Hence, if you have multiple null values, only one will be returned.

Conclusion

As seen in this tutorial, you can use the MySQL DISTINCT clause to fetch unique values from a table field that contains duplicate values.

from Linux Hint https://ift.tt/3wdYRIO