Flash cards
Review the key moves
What is the main idea behind SQL SELECT DISTINCT Statement?
Lesson checks
Practice each idea before moving on
Short Mimo-style checks built from this lesson's code, terms, and sequence.
Which statement best captures the main point of this lesson?
Complete the missing token from the example code.
___ DISTINCT Country FROM Customers;Put the learning moves in the order that makes the concept easiest to apply.
The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (unique) values.
In a table, a column may contain several duplicate values - and sometimes you want to list only the unique values.
Example
SELECT DISTINCT Country FROM Customers;SELECT DISTINCT Syntax
SELECT DISTINCT column1 , column2, ... FROM table_name ;
Demo Database
Below is a selection from the Customers table used in the examples:
| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
|---|---|---|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno TaquerÃa | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
| 4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
| 5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
SELECT Example Without DISTINCT
If you omit the DISTINCT keyword, the SQL statement returns the "Country" value from all the records of the "Customers" table:
Example
SELECT Country FROM Customers;Count Distinct Values
By using the COUNT() function with the DISTINCT keyword, we can count the number of unique countries.
Example
SELECT COUNT(DISTINCT Country) FROM Customers;Note
The COUNT(DISTINCT column_name ) is not supported in Microsoft Access databases.
Here is a workaround for MS Access:
Example
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);You will learn more about the COUNT() function later in this tutorial.