SQL Puzzle | Distinct values from multiple columns 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. Below are the conditions you need to follow.

CASE 1 –

 ```-- c1 -> 1 c2 -> 2 c1 -> 2 c2 -> 1 OUTPUT 1 2 -- ```

CASE 2 –

 ```-- c1 -> 5 c2 -> 5 c1 -> 5 c2 -> 5 OUTPUT 5 5 -- ```

CASE 3 –

 ```-- c1 -> 5 c2 -> 5 OUTPUT no row in this case -- ```

Please check the sample input and the expected output.

Sample Input

c1 c2
1 1
2 2
3 3
4 4
4 5
5 4
6 6
6 6
10 30
10 30

Expected Output

c1 c2
4 5
6 6
10 30

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

 ```-- CREATE TABLE GetRepeatedDataPuzzle ( c1 INT ,c2 INT ) GO INSERT INTO GetRepeatedDataPuzzle VALUES (1,1), (2,2), (3,3), (4,4), (4,5), (5,4), (6,6), (6,6), (10,30), (10,30) GO -- ```

Rules/Restrictions

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

SOLUTION – 1

 ```-- SELECT c1,c2 FROM GetRepeatedDataPuzzle GROUP BY c1,c2 HAVING COUNT(*) > 1 UNION ALL SELECT a.c1 , a.c2 FROM GetRepeatedDataPuzzle a INNER JOIN GetRepeatedDataPuzzle b ON (( a.c1 = b.c2 AND a.c2 = b.c1 ) AND a.c1 < b.c1 ) ORDER BY c1 -- ```

Output-1

 ```-- c1 c2 ----------- ----------- 4 5 6 6 10 30 (3 rows affected) -- ```

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

Related Puzzle

 ```-- https://msbiskills.com/2017/08/11/sql-puzzle-the-distinct-records-multi-col-puzzle/ -- ```

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/