In this article, I am going to explain how to get unique records without using DISTINCT in SQL Server. This detailed article will cover the following topics as follows,
- Introduction
- What is DISTINCT in SQL Server?
- 9 ways to get unique records without using DISTINCT in SQL Server
- Conclusion
First, let's create a database with a table containing some dummy data. Here, I am providing you with the database along with a table containing the records, on which I am showing you the various examples. Let's see.
CREATE DATABASE HostForLIFE_GetUniqueRecords;
PRINT 'New Database ''HostForLIFE_GetUniqueRecords'' Created';
GO
USE [HostForLIFE_GetUniqueRecords];
GO
-->>----Employee Table ------------------->>--
CREATE TABLE [dbo].[Employee] (
EmployeeID INT IDENTITY (31100, 1),
EmployerID BIGINT NOT NULL DEFAULT 228866,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(255) NOT NULL,
DepartmentID VARCHAR(100) NOT NULL,
Age INT NOT NULL,
GrossSalary BIGINT NOT NULL,
PerformanceBonus BIGINT,
ContactNo VARCHAR(25),
PRIMARY KEY (EmployeeID)
);
Next, you can insert data to the tables using the SQL INSERT statement or by adding data directly to the tables in SSMS.
Let's check our table using the following query.
To get the data from the "Employee" table, use the following query.
SELECT * FROM HostForLIFE_GetUniqueRecords..Employee
What is DISTINCT in SQL Server?
In SQL Server, DISTINCT is a keyword used in a SELECT statement to remove duplicate rows from the result set. When you use DISTINCT, the query returns only unique rows based on the columns specified.
Key Points
- In the SELECT clause, DISTINCT is applied to every column. The combination of values in the columns you specify will be taken into account to determine uniqueness.
- Since SQL Server has to check each record to filter out duplicates, this can have an impact on performance, especially when working with a huge result set.
SELECT DISTINCT <column1>, <column2>, ...
FROM <Table_Name>
WHERE <Condition_list>;
Example
SELECT DISTINCT Age, DepartmentID
FROM [dbo].[Employee]
9 ways to get unique records without using DISTINCT in SQL Server
Here are nine alternatives to achieve unique records in SQL Server without using the DISTINCT keyword:
Method 1. Using GROUP BY
The GROUP BY clause can be used to group rows that have the same values in specified columns so that duplicates can be filtered out.
Syntax
SELECT <column1>, <column2>, ...
FROM <Table_Name>
GROUP BY <column1>, <column2>, ...;
Example
SELECT Age, DepartmentID
FROM [dbo].[Employee]
GROUP BY Age, DepartmentID
Method 2. Using ROW_NUMBER()
ROW_NUMBER() can be used to get UNIQUE/DISTINCT records in SQL Server.
Syntax
WITH RankedRecords AS (
SELECT
<column1>,
<column2>,
ROW_NUMBER() OVER (PARTITION BY <column1>, <column2> ORDER BY (SELECT NULL)) AS RowNumber
FROM
<Table_Name>
)
SELECT
<column1>,
<column2>,
...
FROM
RankedRecords
WHERE
RowNumber = 1;
Example
WITH RankedRecords AS (
SELECT
Age,
DepartmentID,
ROW_NUMBER() OVER (PARTITION BY Age, DepartmentID ORDER BY (SELECT NULL)) AS RowNumber
FROM
[dbo].[Employee]
)
SELECT
Age,
DepartmentID
FROM
RankedRecords
WHERE
RowNumber = 1;
Method 3. Using Aggregate Functions
Aggregation functions (such as MIN, MAX, COUNT, etc.) can be used to get unique records on the SQL Server.
Syntax
SELECT
<column1>,
<column2>,
COUNT(*) AS Count
FROM
<Table_Name>
GROUP BY
<column1>,
<column2;
Example
SELECT
Age,
DepartmentID,
COUNT(*) AS Count
FROM
[dbo].[Employee]
GROUP BY
Age,
DepartmentID;
Method 4. Using a Subquery
Subqueries can be used as an alternative to DISTINCT to get unique records in SQL Server.
Syntax
SELECT
<column1>,
<column2>
FROM (
SELECT
<column1>,
<column2>
FROM
<Table_Name>
) AS subquery
GROUP BY
<column1>,
<column2>;
Example
SELECT
Age,
DepartmentID
FROM (
SELECT
Age,
DepartmentID
FROM
[dbo].[Employee]
) AS subquery
GROUP BY
Age,
DepartmentID;
Method 5. Using EXISTS or NON-EXISTS Clause
EXISTS or NON-EXISTS clause can also be used as an alternative to DISTINCT to get unique records in SQL Server.
Syntax
SELECT
<Column1>,
<Column2>,
...
FROM
<Table_Name> t1
WHERE
NOT EXISTS (
SELECT 1
FROM
<Table_Name> t2
WHERE
t1.<Column1> = t2.<Column1>
AND t1.<Column2> = t2.<Column2>
AND t1.ID > t2.ID -- Assuming you have an ID column
);
Example
SELECT
Age,
DepartmentID
FROM
[dbo].[Employee] t1
WHERE
NOT EXISTS (
SELECT 1
FROM
[dbo].[Employee] t2
WHERE
t1.Age = t2.Age
AND t1.DepartmentID = t2.DepartmentID
AND t1.EmployeeID > t2.EmployeeID -- Assuming you have an ID column
);
Method 6. Using CTE (Common Table Expression)
In SQL Server, CTE (Common Table Expression) can be used in place of DISTINCT to get unique records.
Syntax
WITH UniqueRecords AS (
SELECT
<column1>,
<column2>
FROM
<Table_Name>
GROUP BY
<column1>, <column2>
)
SELECT *
FROM
UniqueRecords;
Example
WITH UniqueRecords AS (
SELECT
Age,
DepartmentID
FROM
[dbo].[Employee]
GROUP BY
Age, DepartmentID
)
SELECT *
FROM
UniqueRecords;
Method 7. Using SELF-JOIN
In SQL Server, SELF-JOIN can be used instead of DISTINCT to get unique records.
Syntax
SELECT
T1.<Column1>,
T1.<Column2>,
...
FROM
<Table_Name> T1
INNER JOIN (
SELECT
<Column1>,
<Column2>,
MIN(ID) AS min_id
FROM
<Table_Name>
GROUP BY
<Column1>, <Column2>
) T2
ON T1.ID = T2.min_id;
Example
SELECT
T1.Age,
T1.DepartmentID
FROM
[dbo].[Employee] T1
INNER JOIN (
SELECT
Age,
DepartmentID,
MIN(EmployeeID) AS min_id
FROM
[dbo].[Employee]
GROUP BY
Age, DepartmentID
) T2
ON T1.EmployeeID = T2.min_id;
Method 8. Using INTERSECT
In SQL Server, using INTERSECT in place of DISTINCT is also an option to get unique records.
Syntax
SELECT <column1>, <column2>
FROM <Table_name>
INTERSECT
SELECT <column1>, <column2>
FROM <Table_name>
Example
SELECT Age, DepartmentID
FROM [dbo].[Employee]
INTERSECT
SELECT Age, DepartmentID
FROM [dbo].[Employee]
Method 9. Using UNION
In SQL Server, using UNION in place of DISTINCT is also an option to get unique records.
Syntax
SELECT <column1>, <column2>
FROM <Table_Name>
UNION
SELECT <column1>, <column2>
FROM <Table_Name>
Example
SELECT Age, DepartmentID
FROM [dbo].[Employee]
UNION
SELECT Age, DepartmentID
FROM [dbo].[Employee];
HostForLIFEASP.NET SQL Server 2022 Hosting
