Brief Intro of PostgreSQL DISTINCT And DISTINCT ON Clause

Hi Guys,

In this blog, I will be explaining to you how do we use DISTINCT and DISTINCT On clauses in PostgreSQL.DISTINCT is used for filtering duplicates records from the Resultset. I think everyone knows about the DISTINCT clause.Every database provides this clause.

Syntax : 

SELECT DISTINCT  OR DISTINCT ON (expressions)
expressions
FROM tables
[WHERE conditions];

Parameters or Arguments - 
distinct expressions : Use to remove the duplicates.
expressions : columns that you need to get.
tables : The tables that you wish to get records.
WHERE conditions : The conditions that must be match for the data.

First, we will have created a table for the better understanding of these clauses and insert records into the table.

For Example :   

CREATE TABLE color (
 id serial NOT NULL PRIMARY KEY,
 background VARCHAR,
 foreground VARCHAR
);

INSERT INTO color(background,foreground) VALUES('RED','RED');
INSERT INTO color(background,foreground) VALUES('RED','GREEN');
INSERT INTO color(background,foreground) VALUES('RED','BLUE');
INSERT INTO color(background,foreground) VALUES('BLUE','BLUE');
INSERT INTO color(background,foreground) VALUES('BLUE','WHITE');
INSERT INTO color(background,foreground) VALUES('BLUE',NULL);
INSERT INTO color(background,foreground) VALUES('GREEN','GREEN');
INSERT INTO color(background,foreground) VALUES('GREEN','YELLOW');
INSERT INTO color(background,foreground) VALUES('GREEN','BLACK');
INSERT INTO color(background,foreground) VALUES('WHITE','WHITE');
INSERT INTO color(background,foreground) VALUES('WHITE','BLACK');

DISTINCT Clause WITH SINGLE Column : 

let's Start with the simplest DISTINCT clause example We can use the DISTINCT clause to return a single column that removes the duplicates from the result set.

SELECT DISTINCT background from color.

OUTPUT : 

This PostgreSQL DISTINCT example would return all unique background colors values from the color table.

DISTINCT Clause WITH MULTIPLE Column : 

Now we will see how will you use the PostgreSQL DISTINCT clause for remove duplicates from multiple columns in your SELECT Statement.

SELECT DISTINCT background,foreground from color.

OUTPUT : 

This PostgreSQL DISTINCT clause example would return each unique background and foreground combination from the color table. In this case, the DISTINCT applies to each column listed after the DISTINCT keyword, and therefore returns distinct combinations.

DISTINCT ON Clause WITH MULTIPLE Column : 

PostgreSQL is different  compared to other databases, is that you have one more option when using the DISTINCT clause - which is called DISTINCT ON.
The DISTINCT ON clause will only return the first row for the DISTINCT ON (columns), based on the ORDER BY clause provided in the query. 

Let's explore further how to use DISTINCT ON in the DISTINCT clause, and what it returns.

So we could modify the example above:

SELECT DISTINCT ON (background) background, foreground FROM color ORDER BY background,foreground;

OUTPUT : 

This DISTINCT clause example, which uses the DISTINCT ON keywords, would return all unique background values. But in this case, for each unique
background value, it would return only the first unique background record and counters based on the ORDER BY clause along with the foreground values from that record.

It is not returning the unique background, background combinations. It is, in effect, doing a LIMIT of 1 for each DISTINCT ON (background) and returning the corresponding foreground values after it chooses the records to return.

Thanks

Sanjay Saini