Skip to content

Distinct Keyword in SQL/PostgreSQL

Distinct Keyword in SQL

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.

Distinct Keyword in SQL

Syntax for SQL Distinct in Single Column

DISTINCT column_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.

Distinct Keyword in SQL
Distinct Keyword in SQL

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.

Distinct Keyword in SQL
Getting distinct movie language

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

FROM movies;

The output is

Distinct Keyword in SQL
Distinct movie_lang

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.

DISTINCT ON (column_name1) column_name_alias,

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

The output will be

Distinct Keyword in SQL
Getting multiple distinct values

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.

Leave a Reply

Your email address will not be published. Required fields are marked *