SQL Server : try to find max value of counted row-CodePudding

I am using SQL Server 2012 and I have table like this

| Country | Age  |
 --------- ------ 
| SWEDEN  |  43  |
| SWEDEN  |  17  |
| SWEDEN  |  43  |  
| SWEDEN  |  43  |
| SWEDEN  |  17  |
| GERMANY |  17  |
| GERMANY |  17  |
| GERMANY |  17  |
| GERMANY |  44  |
| GERMANY |  44  |
| SWEDEN  |  43  |
| SWEDEN  |  17  |
| GERMANY |  13  |
| SWEDEN  |  17  |
| SWEDEN  |  43  |

And I can count ages in the country like this

| Country  | Age  | CountOfAge |
 ---------- ------ ------------ 
| GERMANY  |  13  |     1      |
| GERMANY  |  17  |     3      |
| SWEDEN   |  17  |     4      |
| SWEDEN   |  43  |     5      |
| GERMANY  |  44  |     2      |

I want max age in country like this

| Country  | Age  | CountOfAge |
 ---------- ------ ------------ 
| GERMANY  |  17  |    3       |
| SWEDEN   |  43  |    5       |

I tried with this SQL statement:

SELECT  
    X.country, X.age, X.countOfAge
FROM 
    (SELECT country, age, COUNT(age) AS countOfAge
     FROM MOCK
     GROUP BY country, age) X

I count the age but I can't filter the max of counted age

CodePudding user response：

Either RANK() or DENSE_RANK() would provide the same results in this case, but read their documentation to understand the difference in behavior between the two - especially the gaps in numbers returned by RANK().

SELECT
    country,
    age,
    countOfAge
FROM (
    SELECT
        country,
        age,
        COUNT(age) AS countOfAge,
        DENSE_RANK() OVER (PARTITION BY country ORDER BY COUNT(age) DESC) AS ranking
    FROM MOCK
    GROUP BY country, age
) X
WHERE ranking = 1;