How To Get Unique Records Without Using Distinct In SQL Server

Introduction

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,

1. Introduction
2. What is DISTINCT in SQL Server?
3. 9 ways to get unique records without using DISTINCT in SQL Server
4. 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 OnkarSharma_GetUniqueRecords;
PRINT 'New Database ''OnkarSharma_GetUniqueRecords'' Created';
GO

USE [OnkarSharma_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 OnkarSharma_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.

Syntax

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];

See you in the next article, till then take care and be happy learning.

You can connect with me @

• c-sharp corner
• LinkedIn
• Instagram

You may also visit my other articles on SQL Server,

• Difference Between Delete, Truncate, And Drop Statements In SQL Server
• Difference Between Stored Procedure and Function in SQL Server
• Difference Between HAVING And WHERE Clause In SQL Server
• How To Generate Scripts For Database Objects In SQL Server
• INSERT INTO SELECT vs SELECT INTO In SQL Server
• Different Ways To Handle NULL In SQL Server
• DDL Triggers In SQL Server
• IIF Function In SQL Server

Conclusion

In this article, we have discussed how to get unique records without using the DISTINCT keyword In SQL Server with various examples.

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.

Thanks for reading.