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”.

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

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