SQL uses DISTINCT to remove duplicate rows from the result set.
SELECT DISTINCT column1, column2, ... FROM table_name; |
Below is a selection from the "Product" table in the DB2 database.
| ProductID | ProductDesc | Category | SRP | QtyOnHand | TotalValue |
|---|---|---|---|---|---|
| 7001 | Mouse | Accessories | 75.00 | ||
| 7002 | Harddrive | 65.00 | 20 | 1,300 | |
| 7003 | Keyboard | Accessories | 36.00 | 33 | 1,118.00 |
| 7004 | RAM | Components | 23.50 | 16 | 376.00 |
| 7005 | Honda | Bikes | 1,200 | ||
| 7006 | PEN | 7.45 | 10 | 74.50 | |
| 7007 | Keyboard | Accessories | 40.00 | 33 | 1,118.00 |
| 7008 | RAM | Components | 23.50 | 16 | 376.00 |
For getting the unique ProductDesc, you need to use the following query.
SELECT DISTINCT ProductDesc FROM Product; |
The above SQL statement lists the number of different (distinct) ProductDesc in the "Product" table.
| ProductID | ProductDesc | Category | SRP | QtyOnHand | TotalValue |
|---|---|---|---|---|---|
| 7001 | Mouse | Accessories | 75.00 | ||
| 7002 | Harddrive | 65.00 | 20 | 1,300 | |
| 7003 | Keyboard | Accessories | 36.00 | 33 | 1,118.00 |
| 7004 | RAM | Components | 23.50 | 16 | 376.00 |
| 7005 | Honda | Bikes | 1,200 | ||
| 7006 | PEN | 7.45 | 10 | 74.50 |
Note: If you have multiple column names listed after the DISTINCT keyword, The DISTINCT keyword is applied to all columns. It means that the query will use the combination of values in all columns to evaluate the distinction.
If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!