w3resource logo


postgresql distinct

PostgreSQL DISTINCT

Secondary Nav

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

<!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.

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

<!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>


Join our Question Answer community to learn and share your programming knowledge.

Solve these problems:

Java: How to convert a string to an integer in Java?

C#: Loops in c#

SQL: JOIN using more than 5 tables