Sql Distinct | Distinct command in sql - sql tutorial - learn sql
- DISTINCT command in SQL collects the unique or distinct records from a field of a table.
- The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.
- There may be a situation in which you have multiple duplicate records in a table.
- When fetching such records, it makes more sense to fetch only those unique records instead of fetching duplicate records.
- Any value that has a duplicate will only show up once.
SQL Distinct
Syntax
SELECT DISTINCT "column_name"
FROM "table_name";
- "table_name" is the name of the table where data is stored, and "column_name" is the name of the column containing the data to be retrieved.
Examples
The examples will use the following table:
- Table Store_Information
| Store_Name | Sales | Txn_Date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-1999 |
| San Diego | 250 | Jan-07-1999 |
| Los Angeles | 300 | Jan-08-1999 |
| Boston | 700 | Jan-08-1999 |
Read Also
Insert into select.Example 1: Use DISTINCT on one column
- To select all distinct stores in Table Store_Information,
SELECT DISTINCT Store_Name FROM Store_Information;
Result:
Store_Name
Los Angeles
San Diego
BostonExample 2: Use DISTINCT on multiple columns
- We can apply DISTINCT to multiple columns. If we want to get a list showing all unique combinations of stores and transaction dates, we would type in the following,
SELECT DISTINCT Store_Name, Txn_Date FROM Store_InformationResult:
| Store_Name | Txn_Date |
|---|---|
| Los Angeles | Jan-05-1999 |
| San Diego | Jan-07-1999 |
| Los Angeles | Jan-08-1999 |
| Boston | Jan-08-1999 |
Exercises
- For these exercises, assume we have a table called Users with the following data
Table Users
| First_Name | Last_Name | Birth_Date | Gender | Join_Date |
|---|---|---|---|---|
| Sophie | Lee | Jan-05-1960 | F | Apr-05-2015 |
| Richard | Brown | Jan-07-1975 | M | Apr-05-2015 |
| Casey | Healy | Sep-20-1969 | M | Apr-09-2015 |
| Jamal | Santo | Oct-08-1983 | M | Apr-09-2015 |
| Jill | Wilkes | Nov-20-1979 | F | Apr-15-2015 |
Interview questions in distinct command in SQL:
Example 1
1. Which of the following SQL statement is valid ?
a) SELECT DISTINCT * FROM Users;
b) SELECT DISTINCT First_Name FROM Users;
c) SELECT DISTINCT First_Name Last_Name FROM UsersAnswer:
SELECT DISTINCT First_Name FROM Users;
Read Also
Inline view in sql.Example 2
2. What's the result of the following query ?
SELECT DISTINCT Join_Date From Users;
Answer
Join Date
Apr-05-2015
Apr-09-2015
Apr-15-2015Example 3
3. What's the result of the following query ?
SELECT DISTINCT Gender, Join_Date From Users;
Answer
| Gender | Join_Date |
|---|---|
| F | Apr-05-2015 |
| M | Apr-05-2015 |
| M | Apr-09-2015 |
| F | Apr-15-2015 |
SQL DISTINCT Sample Application
SQL Distinct Example
