This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to find the value which is most repeated in the table.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
ID Val ----------- ----------- 1 2 2 3 3 4 4 5 5 6 6 4 7 6 8 9 9 6 10 8 |
Expected Output:
|
1 2 3 |
val TotalCount ----------- ----------- 6 3 |
Create a table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE tbl_Numbers ( ID INT ,Val INT ) GO INSERT INTO tbl_Numbers VALUES (1,2),(2,3) ,(3,4),(4,5) ,(5,6),(6,4) ,(7,6),(8,9) ,(9,6),(10,8) GO |
Solution 1: Using CROSS APPLY
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT TOP 1 n.Val ,t.Cnt as TotalCount FROM tbl_Numbers n CROSS APPLY ( SELECT COUNT(*) Cnt FROM tbl_Numbers t WHERE t.Val = n.Val )t ORDER BY t.Cnt DESC |
Solution 2: Using SUbquery
|
1 2 3 4 5 6 7 8 9 10 |
SELECT a.val ,a.cnt as TotalCount FROM ( SELECT TOP 1 Val , COUNT(*) Cnt FROM tbl_Numbers n GROUP BY Val ORDER BY Cnt DESC )a |
Please try the different solution for this puzzle and share it via comment...
