 |
| DISTINCT clause in DB2 for i SQL |
The SELECT DISTINCT statement is used to return only distinct rows i.e. different rows. It will avoid the duplicate rows to return in any specific column/table. It fetches the unique values.
Syntax of DISTINCT clause
SELECT DISTINCT column1, column2, ... FROM table_name
Create table Customer
Use existing table Customer to understand the functioning of this clause.
Example 1: Select all the different City from Customer Table
SELECT DISTINCT CUSTCITY FROM customer
Output:
CUSTCITY AGRA PATNA HISAR IDUKKI KOLKATA CHENNAI KOCHI
Example 2: Select all the different rows from Customer Table
SELECT DISTINCT * FROM customer
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
1 Amit PATNA BIHAR INDIA
2 Anil PATNA BIHAR INDIA
3 Dhanraj HISAR HARYANA INDIA
4 Udeep KOCHI KERELA INDIA
5 Yatin IDUKKI KERALA INDIA
6 Tinku AGRA UTTAR PRADESH INDIA
7 Mounish KOLKATA WEST BENGAL INDIA
8 Annamalai CHENNAI TAMIL NADU INDIA
9 Kannan CHENNAI TAMIL NADU INDIA
Example 3: Select all the different rows from Customer Table based on list of expressions
SELECT DISTINCT CUSTCITY, CUSTSTATE, CUSTCOUNTRY FROM customer
Output:
CUSTCITY CUSTSTATE CUSTCOUNTRY AGRA UTTAR PRADESH INDIA HISAR HARYANA INDIA KOLKATA WEST BENGAL INDIA KOCHI KERELA INDIA IDUKKI KERALA INDIA PATNA BIHAR INDIA CHENNAI TAMIL NADU INDIA