DISTINCT
The DISTINCT keyword is used together with the SELECT statement to remove duplicate records and obtain only unique records.
When there are multiple duplicate records in a table, the DISTINCT keyword is used to obtain only unique records instead of duplicate records.
Syntax
SELECT DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | [column, ...] }
[ FROM from_item [, ...] ];
Parameter Description
- DISTINCT [ ON ( expression [, …] ) ]
Removes all duplicate rows from the result set of the SELECT statement so that each row in the result set is unique.
Retains only the first row in the set of rows that have the same result computed on the given expression.
Examples
Insert the following data into the customer_t1 table:
openGauss=# INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name,Amount) VALUES
(6881, 'maps', 'Lily',1000),
(4320, 'tpcds', 'Lily',2000);
The current data is as follows:
openGauss=# SELECT * FROM customer_t1 ;
c_customer_sk | c_customer_id | c_first_name | c_last_name | amount
---------------+---------------+--------------+-------------+--------
3869 | hello | Grace | | 1000
3869 | hello | Grace | | 1000
3869 | | Grace | |
3869 | hello | | |
3869 | hello | | |
| | | |
6985 | maps | Joes | | 2200
9976 | world | James | | 5000
4421 | Admin | Local | | 3000
6881 | maps | Lily | | 1000
4320 | tpcds | Lily | | 2000
(11 rows)
Query all values of c_first_name in the customer_t1 table. There are two Lily and three Grace.
openGauss=# SELECT c_first_name FROM customer_t1 ;
c_first_name
--------------
Grace
Grace
Grace
Joes
James
Local
Lily
Lily
(11 rows)
Use the DISTINCT keyword in the SELECT statement. The result shows that duplicate data has been deleted.
openGauss=# SELECT DISTINCT c_first_name FROM customer_t1 ;
c_first_name
--------------
James
Grace
Local
Joes
Lily
(6 rows)