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
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, ...
You can specify as many columns as you want, but as you’ll see, most times 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
SELECT city FROM parks;
Notice how the cities
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
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:
/* Type your answer below */
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 variations or misspellings 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.
The post SQL DISTINCT Clause For SQL Server appeared first on Essential SQL.