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