Distinct Operator

There may be times when we want to see only one item in a list even though it may appear many times in the database. The keyword DISTINCT will return a single result for each unique value in the column. This example returns only one record for each of the dog colors we have in our table.

SELECT DISTINCT ColorOfDog FROM Dog

OUTPUT

ColorOfDog
—————-
Black
Black and White
Brown
Gray
Red
Tan
White
White and Yellow
Yellow

(9 row(s) affected)

 

If you add another column to the statement, the distinct will take the combination of the two columns. Let‘s add the column BreedOfDog to the statement.

SELECT DISTINCT ColorOfDog, BreedOfDog
FROM Dog
OUTPUT (Modified)

ColorOfDog          BreedOfDog
—————-         ——————————
Black                      Bernese Rottie
Black                      Bleu de Gascogne
Black                      Border Collie
Black                      Broodle Griffon
Black                      Bukovina Sheepdog
Black                      Bulldog
Black                      Cairland Terrier
Black                      Cambodian Razorback Dog
Black                      Care-Tzu
Black                      Carolina Dog

(176 row(s) affected)

In the case of the column ColorOfDog, you will get duplicates rows; however, the combination of color and breed will only appear once.

 

I

Want to follow along on your own database?

Do you want to follow along with the examples?  We have created a sample database you can use to follow along and try in your own database.  Go to the download page and follow the instructions.  Have fun on the SQL trail!