This SQL tutorial focuses on the SQL DISTINCT keyword in SQL Server, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.
This tutorial is a part of several posts explaining how to write basic queries in SQL Server. To read additional posts regarding this subject, please use the following links:
- Writing Basic SQL SELECT statements
- SQL Arithmetic Operators
- SQL Column Aliases
- SQL String Concatenation
- SQL DISTINCT keyword
The SQL Server DISTINCT Keyword
The default display of SQL Server queries is all rows, including duplicate rows. The SQL Server DISTINCT keyword in the SELECT clause is used to eliminate duplicate rows and display a unique list of values.
For example: let us assume we have a Books table, which contains a list of books written by different authors: 4 books by Frank Herbert, 3 books by Steven king, and another 3 books by Virginia Woolf. You were asked to display a unique list of authors:
SELECT author FROM books author ------------- Frank Herbert Frank Herbert Frank Herbert Frank Herbert Steven King Steven King Steven King Virginia Woolf Virginia Woolf Virginia Woolf
The above SQL Server statement is not very helpful since it retrieves 10 rows (Frank Herbert returns 4 times, Steven King Returns 3 times, and Virginia Woolf returns 3 times), whereas our intention was to display only 3 authors.
To resolve this issue and retrieve only unique values, use the SQL Server DISTINCT keyword:
SELECT DISTINCT column_name FROM table_name SELECT DISTINCT author FROM books
The result:
author ------------- Frank Herbert Steven King Virginia Woolf
Note : The SQL Server DISTINCT keyword should be included immediately after the SQL Server SELECT keyword.
SQL Server DISTINCT Statement on Multiple Columns
After the SQL Server DISTINCT keyword, it is possible to specify more than one column. The SQL Server DISTINCT qualifier affects all selected columns, in so doing, the result will display the unique combination of values from the different columns. For example, a list of authors from different cities and countries:
| Author Name | City | Country |
| John Smith | Liverpool | England |
| Sophia Williams | London | England |
| Emma Miller | Liverpool | England |
| Olivia Wilson | Paris | France |
| Logan Davis | Paris | France |
You can see that there are two authors from Liverpool-England, two authors from Paris-France, and another author from London-England.
Executing the SQL Server DISTINCT statement on the Country column results in retrieving 2 rows (England,Paris).
SELECT DISTINCT country FROM authors
Executing the SQL Server DISTINCT statement on the City column results in retrieving 3 rows (Liverpool, London, paris).
SELECT DISTINCT city FROM authors
If you execute the SQL Server DISTINCT statement on both the City and Country columns:
SELECT DISTINCT city , country FROM authors
.Three results are retrieved – Liverpool England, London England, Paris France
