SQL DISTINCT STATEMENT

The Distinct keyword is used with Select sql statement which avoid duplicate records in result set. When you want to retrieve only unique records for specific columns, you ay use distinct keyword in your sql statement.

Let’s understand each with syntax and examples.

MS SQL

Syntax:

SELECT DISTINCT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE CONDITION;

Example:

Original Data in table called “Employee”


Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Josh Wills Male 09-07-1980
3 EMP_1003 Jeremy Parkar Female 08-09-1990
4 EMP_1004 Aidan Walls Male 05-03-1989
5 EMP_1005 Adam Mondry Male 02-06-1991


SELECT Gender FROM Employee
ORDER BY Gender ASC;

Data displayed from table “Employee” after above Sql Query executed


Gender
Female
Male
Male
Male
Male

So here in above result set, we can see Male record is duplicated for 4 times. If we need to remove this duplicate record, we can use following sql statement with Distinct keyword.


SELECT DISTINCT Gender FROM Employee
ORDER BY Gender ASC;

Gender
Female
Male

We can also use GROUP By clause and COUNT aggregate function to get total number of Male and Female with the help of following sql statement.


SELECT Distinct Gender, COUNT(Id) as Total
FROM Employee
GROUP By Gender

Gender Total
Female 1
Male 4