I can not mount a query, the logic is:
Select produtos with categorias different, being the products with more points, thus I will have a product of more points of category X, another of category Y.
I tried to use DISTINCT , something like:
SELECT DISTINCT categoria FROM produtos ORDER BY pontos DESC
The problem is that the columns I need to read do not come (only comes with categoria ..).
What do I do to resolve this case?
According to the information provided in the comments, I believe you want this:
SELECT p.categoria, MAX(p.nome) as nome, p.pontos
FROM produtos p
INNER JOIN
(
SELECT categoria, MAX(pontos) as pontos
FROM produtos
GROUP BY categoria
) pp ON pp.categoria = p.categoria AND pp.pontos = p.pontos
GROUP BY p.categoria, p.pontos
I do not know which column shows the product name so I used nome .
The only reason I used MAX(p.nome) is for situations where a product has the same number of points and the same category so it takes one.
According to what was explained, I made a template like this:
SELECT categoria, produto, pontos
from tabela a
WHERE pontos = (select max(pontos) from tabela b where a.categoria = b.categoria)
order by pontos desc
example: link