PostgreSQL DISTINCT

Last update on November 13 2023 09:57:48 (UTC/GMT +8 hours)

DISTINCT Clause

The PostgreSQL DISTINCT clause is used with a SELECT statement to suppress duplicate values if any in a column. After executing a select statement the resultant table returns all rows according to the provided expression. If it is required to eliminate the duplicate rows from the resultant table the DISTINCT clause in PostgreSQL can be used.

The DISTINCT keyword is written after the SELECT statement to specify one or more columns which will appear once in the resultant table.

The ALL keyword is opposite of DISTINCT if it is used instead of DISTINCT the resultant table retain all rows.

Syntax:

SELECT DISTINCT 
 <column_name> FROM <table_name>

Parameter

Parameter	Description
column_name	Name of the column
table_name	Name of the table

Sample Table Structure

postgresql sample table employee structure

Data available in the table

PostgreSQL DISTINCT example

If we want to fetch unique designame from the employee table the following PostgreSQL SELECT statement can be used.

SQL

SELECT DISTINCT designame FROM employee

Output:

Explanation

The above example shows, only the unique designame have fetched from the employee table where some of the designame have more than once. The DISTINCT clause eliminates the duplicate designame.

PHP with PostgreSQL DISTINCT Clause example 1

Code:

<!DOCTYPE html>
<html lang="en">
<head><meta charset="utf-8">
 <title>PostreSQL PHP DISTINCT Example 1</title>
<meta name="description" content="only the unique designame have fetched from the employee table where some of designame have more than once.">
</head>
<body> 
<h1>The unique designame have fetched </h1> 
<?php$db = pg_connect("host=localhost port=5432 dbname=w3r user=w3r_admin password=admin123");$result = pg_query($db,"SELECT DISTINCT designame  FROM employee");
echo "<table>";while($row=pg_fetch_assoc($result)){echo "<tr>";
echo "<td align='center' width='200'>" . $row['designname'] . "</td>";
echo "</tr>";}
echo "</table>";?>
</body>
</html>

PostgreSQL DISTINCT ORDER BY

If we want to fetch different designations in alphabetically arranged order the following PostgreSQL SELECT statement can be used.

Code:

SELECT DISTINCT designame FROM employeeORDER BY designame

Output:

Explanation

The above example shows, only the unique designame to arrange ascendingly have fetched from the employee table where some of the designame have more than once. The DISTINCT clause eliminates the duplicate designame and ORDER BY have arranged them in ascending order which is a default if specified no order.

PHP with PostgreSQL DISTINCT ORDER BY example 2

Code:

<!DOCTYPE html>
<html lang="en"><head>
<meta charset="utf-8"><title>PostreSQL PHP DISTINCT Example 2</title>
<meta name="description" content="To fetch different designations in alphabatically arranged
 order the following PostgreSQL SELECT statement can be used.">
</head>
<body>
<h1>To fetch different designations in alphabatically arranged  order</h1>
<?php
$db = pg_connect("host=localhost port=5432 dbname=w3r user=w3r_admin password=admin123");
$result = pg_query($db,"SELECT DISTINCT designame  FROM employee ORDER BY designame");
echo "<table>";while($row=pg_fetch_assoc($result)){echo "<tr>";
echo "<td align='center' width='200'>" . $row['designame'] . "</td>";
echo "</tr>";}
echo "</table>";
?>
</body>
</html>

Previous: WHERE Clause
Next: GROUP BY