Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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 Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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/

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