Distinct Keyword in SQL/PostgreSQL
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_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.