DISTINCT and duplicate results

This lesson uses DDI_SQL_Training dataset version 1.0.0 on SQL Server 2019 or later. A customer list has 20 rows, but several customers share a city. If you need a list of cities rather than a list of customers, repeated city values get in the way.

Run the example

SELECT DISTINCT City
FROM dbo.Customers
ORDER BY City;

Read it step by step

1. SELECT City FROM dbo.Customers would return 20 rows, with London and other cities repeated.
2. DISTINCT compares the selected values and keeps one row for each different City. It does not delete or update customer records.
3. ORDER BY City sorts the eight remaining names alphabetically so the result is easy to inspect.

Check the result

There are eight rows because the unmodified v1.0.0 dataset has eight city names. DISTINCT works on the whole selected combination: SELECT DISTINCT City, Region asks for unique city-and-region pairs. Selecting CustomerID too would yield 20 rows because every customer ID is unique.

Practice

Find the distinct Region values in Customers. How many are there?

SELECT DISTINCT Region FROM dbo.Customers ORDER BY Region;