What is SELECT DISTINCT and how do I use it?
The previous lessons have discussed the basics of the SQL SELECT statement. There is an option that can be added to the SELECT statement to return a distinct list. For example, if I wanted to find the distinct list of all values in the Color column of Production.Product, I would execute the query:
SELECT DISTINCT Color
A quick reminder: all of our practice problems and examples use the AdventureWorks sample database provided by Microsoft. For some help getting this set up, take a look at our introduction to SQL post!
Instead of returning all 504 rows of the Production.Product table’s color column, this query returns only the ten unique color values. Quickly being able to find the distinct values of a certain column can be very helpful. If we wanted to find the distinct list of job titles for employees in the AdventureWorks company, we could use the query:
SELECT DISTINCT JobTitle
It is possible to create a SELECT DISTINCT statement on more than one column. To find the list of distinct combinations of FirstName and LastName values from the Person.Person table, use the query:
SELECT DISTINCT FirstName, LastName
Using the lessons learned in a previous section about column aliases, we can write a SELECT DISTINCT SQL statement and use column aliases at the same time:
SELECT DISTINCT FirstName AS “First Name”, LastName AS “Last Name”
Complete the following practice problems to gain more experience using SELECT DISTINCT in your SQL queries.
- Find the distinct list of values in the Size column from Production.Product.
- Find the distinct MaritalStatus column values in the HumanResources.Employee table.
- Find the distinct list of CardType values from the Sales.CreditCard table.