Distinct

It will often be the case that several rows will contain duplicate data in a specified column and that you will only want to view one to represent the group. For example, suppose you wanted to find out which employees had made sales in the SALES table.

If you were to use:

    SELECT E_NUM
    FROM SALES;

you would get:

    E_NUM
    ------
    101
    102
    101
    ------

In this case, Employee number 101 has been reported twice even though once is enough. In this case, you would use the DISTINCT operator to filter out duplicates.

    SELECT DISTINCT E_NUM
    FROM SALES;

You would get:

    E_NUM
    ------
    101
    102
    ------

Another common usage of distinct is to use it with a COUNT such as:

    SELECT num_successful_salesmen  = 
           COUNT (DISTINCT E_NUM)
    FROM SALES;

which would produce:

    num_successful_salesmen
    ------
    2
    ------

Counting Records
Introduction to Databases for the Web | Table of Contents
Averages