SQL SELECT DISTINCT

SELECT DISTINCT returns only unique values (without duplicates).

DISTINCT operates on a single column.

DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc.

Example

#

List all French customer cities (without duplicates).

SELECT DISTINCT City
  FROM Customer
 WHERE Country = 'France'
Result:  9 records
City
Lille
Lyon
Marseille
Nantes
Paris
Reims
Strasbourg
Toulouse
Versailles

Syntax

DISTINCT syntax.

SELECT DISTINCT column-name
  FROM table-name

DISTINCT syntax with COUNT or other aggregates.

SELECT COUNT (DISTINCT column-name)
  FROM table-name

More Examples

SELECT DISTINCT

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List all unique supplier countries in alphabetical order.
SELECT DISTINCT Country
  FROM Supplier
ORDER BY COUNTRY
Result: 16 rows
Country
Australia
Brazil
Canada
Denmark

SELECT DISTINCT, with COUNT

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: Find the number of unique supplier countries.
SELECT COUNT (DISTINCT Country) AS Number
  FROM Supplier
Result:
Number
16

You may also like



Guides


vsn 3.1