SQL Puzzle | Deal with Transpose Record | SQL Interview Question

In this puzzle you have to ignore the Transpose Record. Example. For the below 2 records we need any one record-

(1,10,15),
(1,15,10)

For more details please see the sample input and expected output.

Sample Input

Expected Output

Script – DDL and INSERT Sample Data

--

CREATE TABLE DuplicateRecs
(
	 Id INT 
	,Vals1 INT   
	,Vals2 INT
)
GO

INSERT INTO DuplicateRecs VALUES
(1,10,15),
(1,15,10),
(2,10,20),
(2,20,10),
(3,10,15)
GO

INSERT INTO DuplicateRecs VALUES
(4,10,10),
(4,10,10)
GO

SELECT * FROM DuplicateRecs
GO


--

SOLUTION – 1

--

SELECT Id,Vals1,Vals2 from
(
	SELECT * , ROW_NUMBER() OVER(PARTITION BY id,vals1+vals2 order by id) rnk
	FROM DuplicateRecs
)x WHERE rnk = 1 

--

Output – 1

--

Id          Vals1       Vals2
----------- ----------- -----------
1           10          15
2           10          20
3           10          15
4           10          10

(4 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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.

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