SQL Puzzle | 5 Ways to GET DISTINCT DATA without using distinct Keyword | SQL Interview Question

In this post we will understand what are the multiple ways to find distinct rows without using distinct Keyword.

As far as I know methods we can use here are-

• 1. Using UNION
• 2. Using RowNumber
• 3. Using GroupBy
• 4. Using EXCEPT
• 5. Using INTERSECT

Sample script to create table and different methods are given below.

--

--Multiple Ways to Find Distinct Values

CREATE TABLE Duplicate
(
	ID INT
)
GO

INSERT INTO Duplicate(ID) VALUES (1),(1),(2),(2),(2),(1),(1),(1)
GO

SELECT * FROM Duplicate
GO

--

Current Data

--

ID
-----------
1
1
2
2
2
1
1
1

(8 rows affected)


--

Sample Testing

–Method 1 – Using UNION

--

SELECT ID from Duplicate
UNION
SELECT TOP 1 ID FROM Duplicate


--

OUTPUT

--

ID
-----------
1
2

(2 rows affected)



--

–Method 2 – Using Row Number

--

SELECT ID 
FROM 
(
	SELECT ID , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) rnk FROM Duplicate 
)a
WHERE rnk = 1

--

OUTPUT

--

ID
-----------
1
2

(2 rows affected)



--

–Method 3 – Using Group By

--

SELECT ID FROM Duplicate GROUP By ID

--

OUTPUT

--

ID
-----------
1
2

(2 rows affected)



--

–Method 4 – Using EXCEPT

--

SELECT ID from Duplicate
EXCEPT
SELECT NULL /* Provide any value which you do not expect in the table*/

--

OUTPUT

--

ID
-----------
1
2

(2 rows affected)



--

–Method 5 – Using INTERSECT

--

SELECT ID from Duplicate
INTERSECT
SELECT ID FROM Duplicate

--

OUTPUT

--

ID
-----------
1
2

(2 rows affected)



--

Please add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn. Thanks in advance.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com