w3resource

PostgreSQL DISTINCT

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 sample table employee

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:

postgresql distinct example1

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:

postgresql distinct order by example

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



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/PostgreSQL/distinct.php