Controlling duplicate return values

You can apply the ALL, UNIQUE, or DISTINCT keywords to indicate whether duplicate values are returned, if any exist. If you do not specify any of these keywords in the Projection clause, all qualifying rows are returned by default.

ALL: Default. Specifies that all qualifying rows are returned, regardless of whether duplicates exist.
DISTINCT: Excludes duplicates of qualifying rows from the result set
UNIQUE: Excludes duplicate. UNIQUE is a synonym for DISTINCT, as an extension to the ANSI/ISO standard.

For example, the next query returns all the unique ordered pairs of values from the stock_num and manu_code columns in rows of the items table. If several rows have the same pair of values, that pair appears only once in the result set:

SELECT DISTINCT stock_num, manu_code FROM items;

For information on how the database server identifies duplicate NCHAR and NVARCHAR values in a database that has the NLCASE INSENSITIVE property, see NCHAR and NVARCHAR expressions in case-insensitive databases.

You can specify the DISTINCT or UNIQUE keyword of the SELECT statement no more than once in each level of a query or subquery. The following example uses DISTINCT in both the query and in the subquery:

SELECT DISTINCT stock_num, manu_code FROM items 
   WHERE order_num = (SELECT DISTINCT order_num FROM orders
      WHERE customer_num = 120);

The example above is valid, because DISTINCT is used no more than once in each of the SELECT statements.

If a query includes the DISTINCT or UNIQUE keyword (rather than the ALL keyword or no keyword) in the Projection clause whose Select list also includes an aggregate function whose argument list begins with the DISTINCT or UNIQUE keyword, the database server issues an error, as in the following example: .

SELECT DISTINCT COUNT(DISTINCT ship_weight)
       FROM orders;

That is, it is not valid in the same query for both the Projection clause and for an aggregate function to restrict the result set to unique values. (In the example above, replacing either of the DISTINCT keyword with UNIQUE would not avoid this error.)

Queries with multiple aggregate expressions

If the Projection clause does not specify the DISTINCT or UNIQUE keyword of the SELECT statement, the query can include multiple built-in aggregate functions that each includes the DISTINCT or UNIQUE keyword as the first specification in the argument list, as in the following example:

SELECT COUNT (DISTINCT customer_num), 
       COUNT (UNIQUE order_num), 
       AVG(DISTINCT ship_charge) FROM orders;

Support for multiple DISTINCT or UNIQUE aggregate expressions in the same level of a query applies to built-in aggregate functions, but not to user-defined aggregate (UDA) functions that the CREATE AGGREGATE statement defined. The database server issues an error if the argument lists of more than one UDA expression in the same query begin with the DISTINCT or UNIQUE keywords.