SQL DISTINCT Clause For SQL Server

Use the SQL DISTINCT clause to return a unique list of values from a SQL SELECT statement. With DISTINCT, you elect to return unique values based on a combination of one or more columns.

SQL DISTINCT Example

The DISTINCT clause is used with the SELECT statement. It is placed immediately after SELECT and before the columns you wish to select. Here is a general form for the command:

SELECT DISTINCT column1, column2, ...
FROM Table

You can specify as many columns as you want, but as you’ll see, most time you’ll use just a couple of columns.

Let’s try an example. To get a unique list of cities that have had a pro base ball park you can write:

SELECT DISTINCT 
	   city
FROM parks;

Click Run Query to try it!

Here is the same query without DISTINCT…

SELECT city
FROM parks;

Notice how the cities Altoona, Atlanta, and Baltimore are repeated. In this query every city for the parks listed is retrieved!

As you saw from the beginning of our article, you can use DISTINCT with several columns. Doing so, instructs SQL to return the various unique column combination found.

For example, here’s is how we can find a unique list of State and Cities.

SELECT DISTINCT 
	   city,
           state
FROM parks;

Now You Try It!

Let’s find all the unique countries and cities that player or managers were born. To do this you can use the people table, shown below.

Using the space below, write a query to get a unique list of their birth countries and cities:

-- Answer
SELECT DISTINCT 
	   birthcountry, birthcity
FROM people;

Practical uses of SQL Distinct

I like to use DISTINCT when I’m exploring a new data set. It makes it easy to see if there are any variation or misspelling to look out.

I use this as part of my three steps to writing a query. If you’re interested, check out the steps I take to write complex queries.

In addition, if you have some raw data, and you’re looking to create reference or “lookup” tables, then using a sql distinct with queries is a great way to get the data you’ll insert into those tables.

About the Sample Data

Note: This articles uses Lahmans’ Baseball Database. It is a wonderful compilation of batting and pitching statistics from 1871 to 2018! Whether you’re a baseball fan or not, you will find the data interesting for great SQL queries. Read this documentation to learn more about the table and where to get the database for your own use.