REQUEST COMPLIMENTARY SQLS*PLUS LICENCE
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
MORE NEWS
PreambleNoSql is not a replacement for SQL databases but is a valid alternative for many situations where standard SQL is not the best approach for...
PreambleMongoDB 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...
PreambleMS 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...
PreambleAtom 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...
PreambleMongoDB 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...
PreambleSQLShell 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...
PreambleWriting 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...
PreambleOracle Coherence is a distributed cache that is functionally comparable with Memcached. In addition to the basic function of the API cache, it...
PreambleIBM 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....
PreambleIf 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...
PreambleStarting with Microsoft SQL Server 2008, by default, the group of local administrators is no longer added to SQL Server administrators during the...