Step 1: Understanding SELECT DISTINCT

The DISTINCT keyword is used in a SELECT statement to remove duplicate rows from the result set.

Basic syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;
  • DISTINCT applies to all columns listed in the SELECT.
  • If any column values differ, the row is considered unique.

Step 1: Example table

Assume the Employees table:

EmployeeID FirstName LastName Department Salary
1 John Doe IT 5000
2 Jane Smith HR 4500
3 Mike Brown IT 6000
4 Sara White HR 4700
5 Tom Green IT 5200

Step 2: Remove duplicate values from a single column

Suppose we want the unique departments:

SELECT DISTINCT Department
FROM Employees;

Result:

Department
IT
HR

Only unique department names are returned.

Step 3: Remove duplicates based on multiple columns

DISTINCT works on all columns listed. The combination of values must be unique.

Example: Unique combination of Department and Salary:

SELECT DISTINCT Department, Salary
FROM Employees;

Result:

Department Salary
IT 5000
IT 6000
IT 5200
HR 4500
HR 4700

Each row is unique across both columns.

Step 4: Using DISTINCT with expressions

You can also use DISTINCT with calculated values:

SELECT DISTINCT Department, Salary * 12 AS AnnualSalary
FROM Employees;

Result:

Department AnnualSalary
IT 60000
IT 72000
IT 62400
HR 54000
HR 56400

DISTINCT ensures no duplicate combination of Department and AnnualSalary.

Step 5: Using DISTINCT with ORDER BY

You can sort the unique results:

SELECT DISTINCT Department
FROM Employees
ORDER BY Department ASC;

Result:

Department
HR
IT

ORDER BY is applied after duplicates are removed.

Categorized in:

SQL Server,