Summary -
In this topic, we described about the Distinct Clause with detailed example.
DISTINCT clause is used to fetch distinct values from table by removing duplicate values. We may have duplicate values in the table, if we want to fetch identical values, we can use DISTINCT clause in SELECT statement. DISTINCT clause can also use with aggregate functions like COUNT (), AVG( ), MAX( ).
Syntax -
SELECT DISTINCT column1, column2, …, columnN
FROM table_name
[WHERE condition];
- column1, column2, …, columnN – Specifies the columns that are using to fetch from table.
- table_name– Specifies the name of the table.
Example -
Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.
employee_details -
| emp_id | emp_name | designation | manager_id | date_of_hire | salary | dept_id |
|---|---|---|---|---|---|---|
| 001 | Employee1 | Director | 2019-07-11 | 45000.00 | 1000 | |
| 002 | Employee2 | Director | 2019-07-11 | 40000.00 | 2000 | |
| 003 | Employee3 | Manager | Employee1 | 2019-07-11 | 27000.00 | 1000 |
| 004 | Employee4 | Manager | Employee2 | 2019-10-08 | 25000.00 | 2000 |
| 005 | Employee5 | Analyst | Employee3 | 2019-07-11 | 20000.00 | 1000 |
| 006 | Employee6 | Analyst | Employee3 | 2019-10-08 | 18000.00 | 1000 |
| 007 | Employee7 | Clerk | Employee3 | 2019-07-11 | 15000.00 | 1000 |
| 008 | Employee8 | Salesman | Employee4 | 2019-09-09 | 14000.00 | 2000 |
| 009 | Employee9 | Salesman | Employee4 | 2019-10-08 | 13000.00 | 2000 |
Scenario– Fetch distinct rows of one column from table.
Requirement1 – Fetch distinct designation from the employee_details table. The query was as follows –
SELECT DISTINCT designation FROM employee_details;
By executing above query, we can get results as shown below –
| designation |
|---|
| Analyst |
| Clerk |
| Director |
| Manager |
| Salesman |
Requirement2 – Fetch distinct salaries of employees from high to low. The query was as follows –
SELECT DISTINCT salary FROM employee_details ORDER BY salary DESC;
By executing above query, we can get results as shown below –
| salary |
|---|
| 45000.00 |
| 40000.00 |
| 27000.00 |
| 25000.00 |
| 20000.00 |
| 18000.00 |
| 15000.00 |
| 14000.00 |
| 13000.00 |
Scenario – Fetch distinct total number of departments from table.
Requirement – Fetch distinct total number of departments from the employee_details table. The query was as follows –
SELECT COUNT( DISTINCT dept_id ) AS 'Total departments'
FROM employee_details;
By executing above query, we can get results as shown below –
| Total departments |
|---|
| 2 |
Scenario – Fetch distinct rows from two columns.
Requirement – Fetch distinct rows of emp_name, designation from employee_details table. The query was as follows –
SELECT DISTINCT emp_name, designation FROM employee_details;
By executing above query, we can get all the distinct rows of emp_name, designation in the employee_details table. Instead of checking single column it checks the combination of two values. The output was as follows -
| emp_name | designation |
|---|---|
| Employee1 | Director |
| Employee2 | Director |
| Employee3 | Manager |
| Employee4 | Manager |
| Employee5 | Analyst |
| Employee6 | Analyst |
| Employee7 | Clerk |
| Employee8 | Salesman |
| Employee9 | Salesman |
Scenario – Fetch distinct rows with specified condition.
Requirement - Fetch distinct rows of salaries of employees who belongs to dept _ id is 1000. The query was as follows -
SELECT DISTINCT salary FROM employee_details WHERE dept_id = 1000;
By executing above query,we can get results as shown below -
| Salary |
|---|
| 45000.00 |
| 27000.00 |
| 20000.00 |
| 18000.00 |
| 15000.00 |