DISTINCT

Examples

The following example lists only the unique divisions that exist in the Q.ORG table:

This query:

SELECT DISTINCT DIVISION
FROM Q.ORG

Produces this report:

DIVISION
---------
CORPORATE
EASTERN
MIDWEST
WESTERN

Compare this result with the following example:

This query:

SELECT DIVISION
FROM Q.ORG

Produces this report:

DIVISION  
----------
CORPORATE 
EASTERN   
EASTERN   
EASTERN   
MIDWEST   
MIDWEST   
WESTERN   
WESTERN   

DISTINCT can also select distinct combinations of data. For example:

SELECT DISTINCT DEPT, JOB
 FROM Q.STAFF
 ORDER BY DEPT

The report that is produced from this example shows the jobs that are represented in every department.

Remember these properties when you use DISTINCT:

• DISTINCT comes after SELECT.
• DISTINCT comes before the first column name and is not separated from the column name with a comma.
• DISTINCT applies to all the columns that are selected.

DISTINCT can be used with COUNT.

Use DISTINCT with other column functions when you want only the distinct values for the columns within a group to be used. For example, AVG(DISTINCT PRICE) ignores duplicate prices in the column and averages a list in which each price appears once. AVG(PRICE) averages all the prices in the column without regard to the fact that some prices are duplicates.

To list the different values that appear for YEARS, use a query like the following query:

SELECT DISTINCT YEARS
FROM Q.STAFF
ORDER BY YEARS

To list the department numbers for departments in which at least one employee has 10 or more years of service, use a query like the following query:

SELECT DISTINCT DEPT
FROM Q.STAFF
WHERE YEARS >= 10