>> DISTINCT Clause in SQL

DISTINCT is used in SQL to get the distinct or unique data ,ie, to get data without duplicate.

Let's take a table EMPINFO with below data:

POSITION	SALARY	DEPTNUM
PRESIDENT	5000	10
MANAGER	2850	30
MANAGER	2975	10
MANAGER	2975	20
ANALYST	3000	20	DUPLICATE ROWS
ANALYST	3000	20
CLERK	2975	20
SALESMAN	2975	30
SALESMAN	1250	30	DUPLICATE ROWS
SALESMAN	1250	30
SALESMAN	1500	30
CLERK	1100	20
CLERK	1300	30
CLERK	1300	10

In above table, two records are duplicate (all three columns , ie all columns under consideration, are having same data. )

To remove the duplicate data and display only the unique data :

SELECT DISTINCT * FROM EMPINFO;

POSITION	SALARY	DEPTNUM
CLERK	1300	30
ANALYST	3000	20
SALESMAN	1500	30
MANAGER	2850	30
MANAGER	2975	10
SALESMAN	1250	30
MANAGER	2975	20
SALESMAN	2975	30
PRESIDENT	5000	10
CLERK	2975	20
CLERK	1300	10
CLERK	1100	20

Suppose , we want to know the different kinds of  POSITIONS available  in the office :

SELECT DISTINCT POSITION FROM EMPINFO ; -- the column under consideration is POSITION only.

POSITION

CLERK

SALESMAN

PRESIDENT

MANAGER

ANALYST

To display the POSITIONS in ascending order :

SELECT DISTINCT POSITION FROM EMPINFO ORDER BY POSITION;

POSITION

ANALYST

CLERK

MANAGER

PRESIDENT

SALESMAN

We can get unique records from all columns or single column or any combination of columns . It will return the unique records from the combination of the columns into consideration.

To get unique JOB and Department data :

SELECT DISTINCT POSITION, DEPTNUM FROM EMPINFO ; -- in this the unique records for the combination of  POSITION and DEPTNUM are displayed.

POSITION	DEPTNUM
PRESIDENT	10
MANAGER	20
CLERK	10
SALESMAN	30
ANALYST	20
MANAGER	30
MANAGER	10
CLERK	30
CLERK	20

NEXT

Go To Main SQL Page


.