Problem to mount query

Posted on

Question :

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?

    

Answer :

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

    

Leave a Reply

Your email address will not be published. Required fields are marked *