DB2 graphic  QMF Version 8

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 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:


Go to the previous page Go to the next page

Downloads | Library | Support | Support Policy | Terms of use | Feedback
Copyright IBM Corporation 1982,2004 Copyright IBM Corporation 1982, 2004
timestamp Last updated: March, 2004