You have equal id and category, so it is a composite primary key, can bring what you look for with a subquery in HAVING, so it will work:
SELECT id, categoria, quantidade
FROM categoria cat
GROUP BY id, categoria, quantidade
HAVING quantidade = (SELECT MAX(quantidade) FROM categoria WHERE categoria = cat.categoria)
ORDER BY categoria, id
Another way is with a Function window:
SELECT id, categoria, quantidade
FROM (
SELECT id,
categoria,
quantidade,
ROW_NUMBER() OVER( PARTITION BY categoria ORDER BY quantidade DESC ) AS rank
FROM categoria cat
) AS categoria_ranqueada
WHERE categoria_ranqueada.rank = 1
ORDER BY categoria, id
Any problems just inform.
You have two ids equal in the same table? By the way, which bank is?
– Jéf Bueno
Give a check on my answer, it should work.
– Tiago Oliveira de Freitas
Did you get the query you wanted ? I accept an answer as correct
– Marco Souza