SQL Puzzle | The Remove Duplicate UOM Puzzle

Puzzle Statement

This is cool puzzle. this comes to my mind while working on a recent assignment. Here you to find distinct values of ID & UOM. We have to remove null and blank values of UOM if they have multiple UOMs against single ID. E.g. In Case of ID = 1 We have to remove NULL UOM but we have to consider ID=5 as it has only 1 row. Please go through the sample input and expected output for details.

Sample Input

ID	UOM
1	Electric
1	Electric
1	NULL
2
2	Water
3	Waste
3	Recycling
4	NULL
4	NULL
5

Expected Output

ID	UOM
1	Electric
2	Water
3	Waste
3	Recycling
4	NULL
5

Rules/Restrictions – The solution should be should use “SELECT” statement or “CTE”.

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

--



CREATE TABLE Measure
(
	 ID INT
	,UOM VARCHAR(100)
)
GO

INSERT INTO Measure
VALUES
(1,'Electric'),
(1,'Electric'),
(1,NULL),
(2,''),
(2,'Water'),
(3,'Waste'),
(3,'Recycling'),
(4,NULL),
(4,NULL),
(5,'')
GO



--

SOLUTION 1 | Using Ranking Function

--


SELECT ID, UOM1 UOM
FROM
(
	SELECT ID, CASE WHEN (UOM) = '' OR UOM IS NULL THEN MAX(UOM) OVER (PARTITION BY ID) ELSE UOM END UOM1
	FROM Measure
)r
GROUP BY ID, UOM1


--

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

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com