Tags

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


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

ID col1 col2 col3 col4
1 a b c d
2 a b c d
3 a b c d
4 g h i j

Expected Output

Id col1 col2 col3 col4
1 a b c d
2 a b c d
3 a b c d

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

Advertisements