Distinct in SQL removes all duplicate rows and maintains only one row for a group of duplicate rows.
While working with huge data there may be a situation when you have multiple duplicate records in a table. While fetching such records, you want to fetch only unique/distinct records instead of fetching duplicate records.
For such type of cases, DISTINCT is an keyword in SQL/PostgreSQL. It allows to show unique and distinct values.
Syntax for SQL Distinct in Single Column
SELECT
DISTINCT column_name
FROM
table_name;
Here the values in the column_name column are used to evaluate the duplicate.
Understanding with an example
For example, we have a movies table.
First, let’s select all the data from movies table as shown in below image.
As you can see, movies table have total 53 records.
Now suppose you need to check that how many languages are there for movies.
Simply write the below query.
SELECT movie_lang
FROM movies;
Below is the output.
But as you can see there is a lot of duplicate values and you need unique values.
For this you need DISTINCT keyword.
--Getting distinct movies language from movies table
SELECT DISTINCT movie_lang
FROM movies;
The output is
Now what about taking multiple columns like movies language with director’s id.
Syntax for SQL Distinct in Multiple Column
If you specify multiple columns, the DISTINCT clause will evaluate the duplicate based on the combination of values of these columns.
SELECT
DISTINCT ON (column_name1) column_name_alias,
column_name2
FROM
table_name
ORDER BY
column_name1,
column_name2;
Here the combination of values in both column_name1 and column_name2 columns will be used for evaluating the duplicate.
To make the result set predictable always use the ORDER BY clause with the DISTINCT ON.
Example for Distinct in Multiple Column
Let’s understand this with an example
--Getting multiple distinct values
SELECT DISTINCT movie_lang,directors_id
FROM movies
ORDER BY 1;
The output will be
You can see there are still multiple movie languages and for each movie language there is a corresponding director’s id.
It works with the combination of columns. It is combining the movies language with the director’s id together and as a whole it returns distinct values for each of the column.