Syntax of DISTINCT Clause in PostgreSQL



SELECT DISTINCT Col_1 DISTINCT Col_1 FROM table_name; table_name;







Notes:

1- The DISTINCT clause can be applied to one or more columns of a table.

2- In syntax, the values of column Col_1 are used to evaluate duplicates. It can also be applied to multiple columns. If you specify the columns in the SELECT statement, the DISTINCT clause will evaluate duplicates based on a combination of the values of these columns.

Example: Suppose we have a table named EMPLOYEE.



CREATE TABLE employee ( empid SERIAL, empid SERIAL, fname VARCHAR NOT NULL, fname VARCHAR NOT NULL, lname VARCHAR NOT NULL lname VARCHAR NOT NULL );

-- Inserted some record using the following query INSERT INTO employee(fname, lname) VALUES('Ashish', 'Singh');

INSERT INTO employee(fname, lname) VALUES('Naina', 'Singh');

INSERT INTO employee(fname, lname) VALUES('Sohan', 'Chaurasia');

INSERT INTO employee(fname, lname) VALUES('Virendra', 'Gupta');

INSERT INTO employee(fname, lname) VALUES('Ashish', 'Gupta');

INSERT INTO employee(fname, lname) VALUES('Sohan', 'Maddheshiya');

INSERT INTO employee(fname, lname) VALUES('Naina', 'Singh');







Fetching data from EMPLOYEE table without DISTINCT Clause.



SELECT empid,fname,lname FROM employee;







Result:





PostgreSQL DISTINCT on one column example

The following query will demonstrate you DISTINCT on single column.



SELECT DISTINCT fname FROM employee;







Result:





PostgreSQL DISTINCT on multiple columns

The following query will demonstrate you DISTINCT on multiple column.



SELECT DISTINCT fname,lname FROM employee;







Result:

As we know, DISTINCT on multiple columns evaluate duplicates based on a combination of the values of these columns, in the EMPLOYEE table 'Naina', 'Singh' will be duplicate row for columns fname and lname.

“DISTINCT ON” in PostgreSQL



SELECT DISTINCT ON (Col_1) column_alias, Col_2 FROM table_name ORDER BY Col_1, Col_2;



The order of the result set returned by the SELECT statement is not predictable therefore the first row of the duplicate group is also unpredictable, so it will be good practice if you are using ORDER BY Clause with DISTINCT ON(Expression) to make clear result set.



SELECT DISTINCT ON(fname) fname, lname FROM employee ORDER BY fname, lname







Result:





The real table value is as below

After SELECT query with DISTINCT ON the rows selected as below, red marks highlighted has been selected in query.

In this tutorial, you have learned how to use PostgreSQL SELECT DISTINCT statement to remove duplicate rows from the result set returned by a query.

The DISTINCT Clause responsible to remove duplicate rows from the SELECT statement's result set. The DISTINCT Clause keeps one record for each group of duplicates.PostgreSQL is also provide the DISTINCT ON(expression), using this one we can keep the first row of each group of duplicates. See the following syntax