Dear readers of our blog, we'd like to recommend you to visit the main page of our website, where you can learn about our product SQLS*Plus and its advantages.
 
SQLS*Plus - best SQL Server command line reporting and automation tool! SQLS*Plus is several orders of magnitude better than SQL Server sqlcmd and osql command line tools.
 

REQUEST COMPLIMENTARY SQLS*PLUS LICENCE

Enteros UpBeat offers a patented database performance management SaaS platform. It proactively identifies root causes of complex revenue-impacting database performance issues across a growing number of RDBMS, NoSQL, and deep/machine learning database platforms. We support Oracle, SQL Server, IBM DB2, MongoDB, Casandra, MySQL, Amazon Aurora, and other database systems.

PostgreSQL DISTINCT statement

7 September 2020

PostgreSQL DISTINCT statement

PostgreSQL DISTINCT statement is used to remove duplicates from the result set. DISTINCT can be used only with SELECT operators.

Syntax for DISTINCT statement in PostgreSQL

SELECT DISTINCT | DISTINCT ON (distinct_expressions_id)
expressions_id
FROM tabs
[WHERE conds];

Parameters and statement arguments

  • Expressions_expressions_id – Expressions used to delete duplicates.
  • Column – The column or calculation you want to get.
  • tabs – The tables from which you want to get the records. The FROM operator must specify at least one table.
  • WHERE conds – Optional. The conditions to be met for the records to be selected.

Note:

  • If only one expression is specified in DISTINCT, the query will return unique values for that expression.
  • If several expressions are specified in DISTINCT, the query retrieves the unique combinations for the listed expressions.
  • If DISTINCT ON keywords are specified, the query will return unique values for Different_expressions and other fields for the selected entries based on ORDER BY (limit 1).
  • In PostgreSQL, DISTINCT does not ignore NULL values. Therefore when using DISTINCT in your SQL statement, your resulting set will contain NULL as a separate value.

Example of a DISTINCT statement with one expression

Let’s look at the simplest example of DISTINCT in PostgreSQL. We can use the DISTINCT operator to return a single field that removes duplicates from the result set.
For example:

SELECT DISTINCT last_name
FROM contacts
ORDER BY last_name;

In this PostgreSQL example, DISTINCT will return all unique last_name values from the contacts table.

An example of a DISTINCT statement with multiple expressions

Let’s see how you can use the PostgreSQL DISTINCT statement to remove duplicates from more than one field in your SELECT statement.
For example:

SELECT DISTINCT last_name, city, state
FROM contacts
ORDER BY last_name, city, state;

This example will return each unique combination of last_name, city, and state from the contacts table. In this case, DISTINCT applies to each field specified after the DISTINCT keyword and therefore returns different combinations.

Example of a DISTINCT ON statement

One thing that is unique in PostgreSQL, compared to other databases, is that you have another option when using the DISTINCT operator, which is called DISTINCT ON.

DISTINCT ON will return only the first line for DISTINCT ON (diver_expressions) based on the ORDER BY operator provided in the query. Any other fields listed in the SELECT statement will be returned for this first line. This is similar to running LIMIT in 1 for each combination of DISTINCT ON (Different_expressions).

Let’s have a closer look at how to use DISTINCT ON in a DISTINCT operator and what it returns.
In this way, we could change the example above :

SELECT DISTINCT ON (last_name) last_name, city, state
FROM contacts
ORDER BY last_name, city, state;

This DISTINCT example, which uses DISTINCT ON keywords, will return all unique values of last_name. But in this case, for each unique value of last_name, it will return only the first unique last_name record it encounters, based on the ORDER BY operator together with the city and state values from that record.

It does not return unique combinations of last_name, city, and state. In fact, it performs a LIMIT of 1 for each DISTINCT ON (last_name) and returns the corresponding city and state values after it has selected the returned records.

PostgreSQL: Distinct | Course

 
Tags: , , , ,

MORE NEWS

 

Preamble​​NoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...

Preamble​​MongoDB Conditional operators specify a condition to which the value of the document field shall correspond.Comparison Query Operators $eq...

5 Database management trends impacting database administrationIn the realm of database management systems, moreover half (52%) of your competitors feel...

The data type is defined as the type of data that any column or variable can store in MS SQL Server. What is the data type? When you create any table or...

Preamble​​MS SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a query.SQL Server accepts,...

First the basics: what is the master/slave?One database server (“master”) responds and can do anything. A lot of other database servers store copies of all...

Preamble​​Atom Hopper (based on Apache Abdera) for those who may not know is an open-source project sponsored by Rackspace. Today we will figure out how to...

Preamble​​MongoDB recently introduced its new aggregation structure. This structure provides a simpler solution for calculating aggregated values rather...

FlexibilityOne of the most advertised features of MongoDB is its flexibility.  Flexibility, however, is a double-edged sword. More flexibility means more...

Preamble​​SQLShell is a cross-platform command-line tool for SQL, similar to psql for PostgreSQL or MySQL command-line tool for MySQL.Why use it?If you...

Preamble​​Writing an application on top of the framework on top of the driver on top of the database is a bit like a game on the phone: you say “insert...

Preamble​​Oracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...

Preamble​​IBM pureXML, a proprietary XML database built on a relational mechanism (designed for puns) that offers both relational ( SQL / XML ) and...

  What is PostgreSQL array? In PostgreSQL we can define a column as an array of valid data types. The data type can be built-in, custom or enumerated....

Preamble​​If you are a Linux sysadmin or developer, there comes a time when you need to manage an Oracle database that can work in your environment.In this...

Preamble​​Starting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...