Tags

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


SQL Puzzle | The Maximum Repetition Puzzle

Ok, This is a very simple but interesting interview puzzle. You have a list of numbers and you have to find out maximum repeating number. In below case number 5 is the most repeating number.

Please check out the sample input values and sample expected output below.

Sample Input

Id Val
3 3
4 4
6 4
5 5
7 5
8 5
1 8
2 8

Expected Output

Id Val Cnt
5 5 3

Script

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

--


CREATE TABLE Maxrepetition
(
	 ID SMALLINT
	,Val INT
)
GO

INSERT INTO Maxrepetition
VALUES
(1,8),
(2,8),
(3,3),
(4,4),
(5,5),
(6,4),
(7,5),
(8,5)
GO

CREATE UNIQUE CLUSTERED INDEX Ix_ID ON Maxrepetition(ID)
GO

CREATE NONCLUSTERED INDEX Ix_Val ON Maxrepetition(Val)
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 | Using Group By


--

SELECT ( SELECT TOP 1 Id FROM Maxrepetition t WHERE z.Val = t.Val ) Id , z.* FROM 
(
	SELECT TOP 1 Val , COUNT(*) Cnt
	FROM Maxrepetition s
	GROUP BY Val
	ORDER BY Cnt DESC
)z

--

SOLUTION – 2 | Using Ranking Function


--

SELECT TOP 1 Id,Val,rnk Cnt FROM 
(
	SELECT Id,Val,COUNT(*) OVER ( PARTITION BY Val ORDER BY Val ) rnk
	FROM Maxrepetition
)r 
ORDER BY rnk DESC


--

SOLUTION – 3 | Using CROSS APPLY


--

SELECT TOP 1 s.Id, s.Val,t.Cnt 
FROM Maxrepetition s
CROSS APPLY
(
	SELECT COUNT(*) Cnt FROM Maxrepetition r WHERE r.Val = s.Val
)t
ORDER BY t.Cnt DESC

--

Execution plans for above queries-

Execution Plans for Maximum Repetition

Execution Plans for Maximum Repetition

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

Advertisements