Tags

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


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”.

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

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 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

Advertisements