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
Black and White
White and 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
Black Bernese Rottie
Black Bleu de Gascogne
Black Border Collie
Black Broodle Griffon
Black Bukovina Sheepdog
Black Cairland Terrier
Black Cambodian Razorback Dog
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.
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!