How can I get the top two rows using max condition?

Question

How can I get the top 2 rows returned in this query?

select nomeClube 'Clube', count(Contrato_id_clube) 'NContratos'
from clube, contrato
where (id_clube= Contrato_id_clube)
group by nomeClube
order by count(Contrato_id_clube) desc

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Dec 27 '16 at 22:42

score 0 · Accepted Answer · answered Dec 27 '16 at 19:52

I believe that you want all groups having the maximum count and your example just happens to have two such rows.

select nomeClube 'Clube', count(Contrato_id_clube) 'NContratos'
from clube inner join contrato on id_clube = Contrato_id_clube
group by nomeClube
having count(Contrato_id_club) = (
    select max(c)
    from (
        select count(Contrato_id_clube) c
        from clube inner join contrato on id_clube = Contrato_id_clube
        group by nomeClube
    ) t
)

The inner join inside the nested query may not be necessary but since you didn't specify the relationship between the tables I've duplicated the entire query there.

score -1 · Answer 2 · answered Dec 27 '16 at 19:47

you should put it in an inner select and get top records:

select * from (
select nomeClube 'Clube', count(Contrato_id_clube) 'NContratos'
from clube, contrato
where (id_clube= Contrato_id_clube)
group by nomeClube
order by count(Contrato_id_clube) desc )
limit 2

How can I get the top two rows using max condition?

2 Answers2