SQL Query to Get Distinct Records Without Using Distinct Keyword

Here we are going to see how to retrieve unique (distinct) records from a Microsoft SQL Server’s database table without using the DISTINCT clause.

We will be creating an Employee table in a database called “geeks”.

Creating database:

CREATE DATABASE geeks;

Using database:

USE geeks;

We have the following dup_table table in our geeks database:

CREATE TABLE dup_table(
  dup_id int,
  dup_name varchar(20));

To view the table schema use the below command:

EXEC SP_COLUMNS dup_table;

Adding values into the dup_table Table:

Use the below query to add records to the table:

INSERT INTO dup_table
VALUES
(1, 'yogesh'),
(2, 'ashish'),
(3, 'ajit'),
(4, 'vishal'),
(3, 'ajit'),
(2, 'ashish'),
(1, 'yogesh');

Now we will retrieve all the data from dup_table Table:

SELECT * FROM dup_table;

Now let’s retrieve distinct rows without using the DISTINCT clause.

By using GROUP BY clause:

The GROUP BY clause can be used to query for distinct rows in a table:

SELECT dup_id, dup_name FROM dup_table
GROUP BY dup_id, dup_name;

By using a set UNION operator:

The set UNION operator can also be used to query for distinct rows in a table:

SELECT dup_id, dup_name FROM dup_table
UNION
SELECT dup_id, dup_name FROM dup_table;

By using set INTERSECT operator:

The INTERSECT operator can be used to query for distinct rows in a table:

SELECT dup_id, dup_name FROM dup_table
INTERSECT
SELECT dup_id, dup_name FROM dup_table;

By using CTE & row_number() function:

CTE stands for Common Table Expressions. It can also be used to query for distinct rows in a table with the row_number() function as shown below:

WITH cte (dup_id, dup_name, dup_count)
AS
(SELECT dup_id, dup_name,
 row_number() over (partition BY dup_id,
 dup_name ORDER BY dup_id) AS dup_count
 FROM dup_table)
SELECT * FROM cte WHERE dup_count = 1;