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 “Beginner”.
Creating database:
CREATE DATABASE Beginner;
Using database:
USE Beginner;
We have the following dup_table table in our Beginner 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;