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; ...

Get Practical SQL Handbook, The: Using SQL Variants, Fourth Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.