How get the max value from a group by?

Question

I want to do a GROUP BY by id_municipio and cbo, and than I want to select the cbo with max amount by id_municipio.

I have been trying:

SELECT id_municipio, cbo_2002, COUNT(*) as N_CBO
 FROM basedosdados.br_me_rais.microdados_vinculos
 WHERE ano = 2020 
 AND id_municipio IN (1200401, 3503208, 3543402)
 GROUP BY id_municipio, cbo_2002

And I get something like:

id_municipio | cbo_2002 | N_CBO
-----------------------
1200401      | 3860     | 250 
1200401      | 3548     |  28
1200401      | 4420     |  12
3503208      | 6851     |   5
3503208      | 3860     | 250
3503208      | 2204     | 350
3503208      | 5222     |1000
3543402      | 5222     |   2
3543402      | 7        | 780
3543402      | 6650     | 660

Now, I expect to have one row by each id_municipio and the cbo with the max value (n_cbo), something like:

id_municipio | cbo_2002
-----------------------
1200401      | 3860
3503208      | 5222
3543402      | 7

Answer 1

Try this:

WITH cte AS (
    SELECT
        id_municipio,
        cbo_2002,
        COUNT(*) AS n_cbo
    FROM
        basedosdados.br_me_rais.microdados_vinculos
    GROUP BY
        1, 2
)
SELECT * FROM (
    SELECT
        id_municipio,
        cbo_2002,
        n_cbo,
        ROW_NUMBER() OVER(
            PARTITION BY id_municipio, cbo_2002 ORDER BY n_cbo DESC
        ) AS rn
    FROM cte
)
WHERE rn = 1