SQL Puzzle | Get Duplicate Rows from Multiple Columns

Write a T-SQL query that fetches duplicate Rows from Multiple Columns like col1,col2,col3,col4

Please check out the sample input and the expected output.

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data

--


CREATE TABLE testdupli
(
	ID Int
	,col1 varchar(1)
	,col2 varchar(1)
	,col3 varchar(1)
	,col4 varchar(1)
)
GO

INSERT INTO testdupli VALUES
(1 ,'a', 'b' ,'c', 'd'),
(2 ,'a', 'b' ,'c', 'd'),
(3 ,'a', 'b' ,'c', 'd'),
(4 ,'g', 'h' ,'i', 'j')


--

—

SOLUTION – 1

--

SELECT Id,col1,col2,col3,col4
FROM
(
	SELECT *, COUNT(*) OVER (PARTITION BY col1,col2,col3,col4) rnk
	FROM testdupli
)b
WHERE b.rnk > 1

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com