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 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
SELECT DISTINCT
city
FROM parks;
Click Run Query to try it!
Here is the same query without DISTINCT
…
SELECT city FROM parks;
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;
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 */
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 variations or misspellings to look out.
I use this as part of my three steps to writing a query.
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.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/