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.
Syntax of DISTINCT Clause
in PostgreSQL
Example: Suppose we have a
table named EMPLOYEE.
Fetching
data from EMPLOYEE table without DISTINCT Clause.
Result:
“DISTINCT ON” in
PostgreSQL
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
Syntax of DISTINCT Clause
in PostgreSQL
SELECT
DISTINCT Col_1
FROM
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,
fname 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');
|
SELECT
empid,fname,lname FROM employee;
|
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
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
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.