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.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/