This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and require output data to find most awarded cricket match type which also won 10th worldcup.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
match_id match_name year Worldcup_10th ----------- -------------------------------- ----------- ------------- 1 ICC_MATCH 2005 1 2 ICL_MATCH 2006 1 3 IPL_MATCH 2001 1 4 ICL_MATCH 2006 0 5 ICL_MATCH 1999 0 6 IPL_MATCH 2002 0 7 IPL_MATCH 2000 0 8 IPL_MATCH 1999 0 9 IPL_MATCH 1998 0 10 World_cup 1999 0 11 ICC_MATCH 1999 0 12 ICC_MATCH 2006 0 |
Expected Output:
|
1 2 3 |
match_name -------------------------------- IPL_MATCH |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
create table tbl_cricketMatches ( match_id INT ,match_name Varchar(32) ,[year] INT ,Worldcup_10th INT ) insert into tbl_cricketMatches values (1, 'ICC_MATCH', 2005, 1) ,(2, 'ICL_MATCH', 2006, 1) ,(3, 'IPL_MATCH', 2001, 1) ,(4, 'ICL_MATCH', 2006, 0) ,(5, 'ICL_MATCH', 1999, 0) ,(6, 'IPL_MATCH', 2002, 0) ,(7, 'IPL_MATCH', 2000, 0) ,(8, 'IPL_MATCH', 1999, 0) ,(9, 'IPL_MATCH', 1998, 0) ,(10, 'World_cup', 1999, 0) ,(11, 'ICC_MATCH', 1999, 0) ,(12, 'ICC_MATCH', 2006, 0) |
Solution 1:
|
1 2 3 4 5 6 7 8 |
with ctetest as ( select match_name, count(1) as ct from tbl_cricketMatches group by match_name ) select a.match_name from ctetest a inner join tbl_cricketMatches b on a.match_name = b.match_name and b.Worldcup_10th=1 where ct = (select max(ct) from ctetest) |
Solution 2:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
WITH CTE AS ( SELECT match_name, Worldcup_10th, COUNT(*) over(PARTITION BY match_name) cnt FROM tbl_cricketMatches ) ,CTE2 AS ( SELECT a.match_name ,COUNT(a.cnt) Maxy FROM CTE a INNER JOIN tbl_cricketMatches b ON a.match_name = b.match_name WHERE b.Worldcup_10th = 1 GROUP BY a.match_name ) SELECT match_name FROM CTE2 WHERE Maxy = (SELECT MAX(Maxy) FROM CTE2) |
Please try the different solutions for this puzzle and share it via comment...
