Open In App
Related Articles

SQL Query to Get Distinct Records Without Using Distinct Keyword

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report

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;

Last Updated : 02 Jun, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads