The SQL DISTINCT command used along with the SELECT keyword retrieves only unique data entries depending on the column list you have specified after it. To illustrate the usage of the DISTINCT keyword, we’ll use our Users table introduced in the previous chapters.
| FirstName | LastName | DateOfBirth | Email | City |
| John | Smith | 12/12/1969 | john.smith@john-here.com | New York |
| David | Stonewall | 01/03/1954 | david@sql-tutorial.com | San Francisco |
| Susan | Grant | 03/03/1970 | susan.grant@sql-tutorial.com | Los Angeles |
| Paul | O'Neil | 09/17/1982 | paul.oneil@pauls-email.com | New York |
| Stephen | Grant | 03/03/1974 | sgrant@sgrantemail.com | Los Angeles |
Our Users table has several users in it, and it would be interesting to retrieve a list with all cities where our users live. If we use the statement below, we will get our city list, but there will be repetitions in it, because some in some cases more than one user lives in certain city:
SELECT City
FROM Users
FROM Users
So how do we do get a list with all cities without repeating them? As you have guessed we’ll use the DISTINCT keyword:
SELECT DISTINCT City
FROM Users
FROM Users
The result of the SQL DISTINCT expression above will look like this:
| City |
| New York |
| San Francisco |
| Los Angeles |
Essentially what the DISTINCT keyword does is removing the duplicates from the result set returned by your SELECT SQL statement.
You can use the DISTINCT keyword with more than one column. Please consider the example below:
SELECT DISTINCT LastName, City
FROM Users
FROM Users
What the above statement will do is to return all unique combinations between LastName and City columns. Here is what the result of this statement will be:
| LastName | City |
| Smith | New York |
| Stonewall | San Francisco |
| Grant | Los Angeles |
| O'Neil | New York |
If you have a look at the original table above, you’ll notice that there are two users with identical names (Grant), who happen to live in the same city (Los Angeles). Because the combination of LastName and City values for both this users is not unique, we got only one row with it, when we used the DISTINCT keyword. On the other hand if we add one more column (Email) after the DISTINCT keyword:
SELECT DISTINCT LastName, Email, City
FROM Users
FROM Users
We’ll retrieve both users with last name Grant, simply because they have different emails and thus their entries are unique as far as our SQL statement is concerned:
| LastName | Email | City |
| Smith | john.smith@john-here.com | New York |
| Stonewall | david@sql-tutorial.com | San Francisco |
| Grant | susan.grant@sql-tutorial.com | Los Angeles |
| O'Neil | paul.oneil@pauls-email.com | New York |
| Grant | sgrant@sgrantemail.com | Los Angeles |
No Comment.
Add Your Comment