SQL Puzzle | The Row Number Puzzle

In this puzzle you have to find out distinct values from multiple columns. In the input table you have 2 columns names c1 and c2. E.g.

c1 -> a
c2 -> b

c1 -> b
c2 -> a

The above will be considered as a single value , for these kind of cases we need the first value. In this case it is a,b for c1 and c2 respectively.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--


--


CREATE TABLE DistinctRecordsMultipleColumns
(
	 c1 VARCHAR(10)
	,c2 VARCHAR(10)
)
GO
 
INSERT INTO DistinctRecordsMultipleColumns VALUES
('a','b'),
('b','a'),
('c','d'),
('e','f'),
('g','g'),
('g','g')
GO


--


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution

--
 
;WITH CTE AS 
(
	SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM DistinctRecordsMultipleColumns
)
SELECT c1,c2 FROM 
(
	SELECT a.*
		FROM 
				   CTE a
		INNER JOIN CTE b
			ON a.c1 = b.c2
			and a.c2 = b.c1
		WHERE a.c1 <= a.c2
	UNION ALL
		SELECT k.* 
		FROM 
				  CTE k
		LEFT JOIN CTE k1
		ON k.c1 = k1.c2 AND k1.c1 = k.c2
		WHERE k1.c1 IS NULL
)k
GROUP BY c1,c2
ORDER BY MIN(rnk)

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com