Click here to Skip to main content
14,173,097 members
Click here to Skip to main content
Add your own
alternative version

Stats

2.3K views
2 bookmarked
Posted 25 Apr 2019
Licenced MIT

SQL DISTINCT Clause For SQL Server

, 25 Apr 2019
Rate this:
Please Sign up or sign in to vote.
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 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:

Use DISTINCT to select unique list of people

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

/* Type your answer below */

-- 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 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.

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
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/

You may also be interested in...

Pro

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04 | 2.8.190524.3 | Last Updated 26 Apr 2019
Article Copyright 2019 by essentialSQL
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid