fbpx

SQL Server Distinct Keyword

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:

 

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

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…