Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Distinguishing DISTINCTs

Given a table with two identifying columns, how do you find all the unique combinations? DISTINCT seems like an obvious answer, but more than one user on the Internet complained of getting nothing but syntax errors or confusing results from what seemed like straightforward DISTINCT queries.

This is probably because DISTINCT occurs in two unique forms:

• With columns or expressions

• With aggregates

The syntax of these two cases differs just enough to cloud the occasional user's judgment.

With column names and expressions, you use DISTINCT just once, and it applies to everything that comes after it. It is the first word in the select list and requires no parentheses. When you opt to use DISTINCT this way, you're locked in; ...