SQL SERVER DISTINCT

SQL SERVER DISTINCT keyword is used to remove all the duplicate records and fetching only unique records from any table.

SYNTAX

SELECT DISTINCT Column_name1, column_name2,.. FROM table_Name
WHERE Condition..  

DISTINCT: is a keyword that returns Unique values for specified columns in SQL

Column_name1, column_name2,.. Specify number of columns from the tables. It can be one or more.

 Lets look at an example of Distinct statement in SQL Server.

First we Create a sample table named as EMP, and Insert some records in this table.

 CREATE TABLE dbo.EMP ( EMPID INT NOT NULL, 
 EMP_DEPT VARCHAR(30), 
 EMP_NAME VARCHAR(50), 
 EMP_SALARY NUMERIC(9,2))

 INSERT INTO EMP
 (EMPID, EMP_DEPT, EMP_NAME, EMP_SALARY)
 VALUES
 (101, 'PRODUCTION', 'RAJAT M', 75000.00),
 (102, 'PRODUCTION', 'MUKESH BHATIA', 70000.00),
 (103, 'PRODUCTION', 'MUSKAN MEHTA', 75000.00), 
 (104, 'SALES', 'ROHAN B NARAYA', 45000.00),
 (105, 'SALES', 'SUSHIL DAS', 40000.00),
 (106, 'SALES', 'MANISH', 45000.00),
 (107, 'PRODUCTION', 'RAJESH SINGH', 78000.00),
 (108, 'HR', 'MOHIN KHAN', 50000.00),
 (109, 'HR', 'SUSHANT K SINGH', 55000.00),
 (110, 'HR', 'LAKSHYA RAWAT', 55000.00),
 (111, 'PRODUCTION', 'MANOJ KUMAR', 75000.00),
 (112, 'SALES', 'SUJOY M', 40000.00),
 (113, 'LOGISTIC', 'VINAY AGARWAL', 35000.00),
 (114, 'LOGISTIC', 'MUSTAKIM M',35000.00),
 (115, 'LOGISTIC', 'VIJAY KUMAWAT', 45000.00)

SELECT * FROM dbo.EMP

Now we have a table EMP in database as shown below.

Lets take an example to see how to use a DISTINCT to get unique records from table.

Using DISTINCT on single column

Lets get the unique values for Department in table EMP.

Following statement eliminates the duplicates department values and fetch only unique records from table EMP. 

SELECT DISTINCT EMP_DEPT FROM dbo.EMP

As you can see, DISTINCT eliminates duplicate Department names and returns unique values.

Lets get actual records for department name in EMP table without using a DISTINCT.

SELET EMP_DEPT FROM dbo.EMP

As you can see result, ‘HR’ is coming three times, ‘PRODUCTION’ is coming 5 times , ‘LOGISTIC’ and ‘SALES’ are coming three times .

It means when you use DISTINCT keyword on any column, It eliminates the duplicates values and returns only unique value for that column.

Using DISTINCT on Multiple columns

When Distinct keyword is used with multiple columns, then SELECT Statement returns the unique combination of multiple columns instead of unique individual records.

Lets Get the unique salary for each department.

Following statement, returns a unique combination records in the Departname name and salary from table EMP.

SELECT DISTINCT EMP_DEPT, EMP_SALARY FROM dbo.EMP

As you can see,  It returns a unique combination records in the salary and department  columns from table EMP.

Using DISTINCT WITH Aggreagte function

You can also use DISTINCT keyword with an Aggreagte function. Lets counts the unique Department in employee table.

Following statement, returns the total unique department in employee table.

SELECT COUNT(DISTINCT EMP_DEPT) AS Uniq_Dept_Cnt FROM dbo.EMP

Using DISTINCT WITH WHERE CALUSE

You can also use WHERE CALUSEwhile using distinct in select statement.

Lets check first, how many employees are there in table whose are getting salary greater than 45000.

 

As you can see, there are 8 employees who are getting more than 45000, and if you observe that unique salary after removing duplicate is 5, i.e 78000,75000,70000,55000 and 50000 as shown in above screenshot.

 

Lets get the same result, using Where clause with Distinct caluse to specify condition.

SELECT DISTINCT EMP_SALARY FROM dbo.EMP WHERE EMP_SALARY >45000

As you can see, it returns unique salaries in the salary column for Salary greater than 45000.

Also Read..

SQL WHERE CALUSE

Aggreagate functions