Eliminating Duplicate Columns with DISTINCT

Go Up to Listing Columns to Retrieve with SELECT

In a query returning multiple rows, it may be desirable to eliminate duplicate columns. For example, the following query, meant to determine if the EMPLOYEE table contains employees with the last name, SMITH, might locate many such rows:

EXEC SQL
DECLARE SMITH CURSOR FOR
SELECT LAST_NAME
FROM EMPLOYEE
WHERE LAST_NAME = 'Smith';

To eliminate duplicate columns in such a query, use the DISTINCT keyword with SELECT. For example, the following SELECT yields only a single instance of “Smith”:

EXEC SQL
DECLARE SMITH CURSOR FOR
SELECT DISTINCT LAST_NAME
FROM EMPLOYEE
WHERE LAST_NAME = 'Smith';

DISTINCT affects all columns listed in a SELECT statement.