DISTINCT

Use DISTINCT before the column names in a SQL statement to prevent duplicate rows from being selected. The following example specifies, in effect, "List only the unique divisions that exist in the table Q.ORG":

This query:

SELECT DISTINCT DIVISION
FROM Q.ORG

Produces this report:

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

Compare the result in the previous example with the following:

This query:

SELECT DIVISION
FROM Q.ORG

Produces this report:

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

DISTINCT can also select distinct combinations of data, for example:

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

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

Remember these properties when using 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 being selected.

DISTINCT is also a special case of COUNT (see COUNT). COUNT is not used with a column name, and COUNT(DISTINCT colname) must be used with a column name and cannot be used with an expression.

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.

Write a column function like this:

COUNT(DISTINCT expression)

The parentheses are necessary.

Example of a COUNT(DISTINCT column function):

SELECT COUNT(DISTINCT EDLEVEL), AVG(EDLEVEL)
FROM Q.APPLICANT

Examples:

• List the different values that appear for YEARS:

  SELECT DISTINCT YEARS
  FROM Q.STAFF
  ORDER BY YEARS

• List the department numbers for departments in which at least one employee has 10 or more years of service:

  SELECT DISTINCT DEPT
  FROM Q.STAFF
  WHERE YEARS = 10