Problem to mount query

Asked

Viewed 66 times

3

I cannot mount a query, the logic is:

Select produtos with categorias different products, being the products with more points, so I will have a product of more points of the category X, another of the category Y.

I tried to use DISTINCT, something like:

SELECT DISTINCT categoria FROM produtos ORDER BY pontos DESC

The problem is that, in that comes the columns I need to read (only comes to categoria..).

What do I do to solve this case?

mysql sql query
  • 1

    What other columns do you need to read? If it’s columns with different values, DISTINCT won’t work

  • columns as name, price.. the idea is to list the products by ordering them by the largest number of points, differing from the categories(want 1 product of each category, being this product of higher like). I wonder if I should wear something like Subqueries?

  • 1

    Do you agree that a category may have several products at different prices? In this case you want to display which price and which product?

  • I agree, I want to select products with different categories, being the products with more points, so I will have a product of more points of the category X, another of the category Y.. That’s the idea, you understand? I’m sorry if I didn’t understand you or misinterpret you!

  • 1

    Which DBMS is using?

  • I am using Mysql @Maiconcarraro

Show 1 more comment

2 answers

3


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 don’t know which column shows the name of the product so I used nome.

The only reason I used MAX(p.nome) is for situations where a product has the same amount of points and same category then it takes only one.

  • Grouping by name increased the list too much, since the names were already different, so what you repeated was the categories.. Then I took the name, and I grouped only by category, it happened that it listed beautiful, but the name of the product did not match the points in which it was being referenced, ie name X had 3 points, which in fact it had 2. I believe the problem was GROUP BY with max(), GROUP BY did not group the name more like.. @Maiconcarraro

  • 1

    True, my mistake. What you can do is select INNER JOIN another select by taking the max, are you using which database? @Alexandrec.Caus

  • I’m using Mysql, and I thank you in advance for your help. @Maiconcarraro

  • 1

    @Alexandrec.Caus Updated the response, a verified.

  • I don’t know if it was witchcraft.. but it worked, I’ll try to understand what you did there before you go out applying, thanks @Maiconcarraro , hugs.

  • 1

    That one SELECT within the INNER JOIN will return all categories with their respective highest scores, done this I do a INNER JOIN with the same table looking for the product that contains that category and points.

  • 1

    I just did the MAX(p.nome) to ensure that it will only return one product per category, in case they are tied in points :)

Show 2 more comments

1

According to what was explained, I made a model 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: http://sqlfiddle.com/#! 9/de8e4/1

  • 1

    Your SQL has only one problem, when you have TWO products with the same categoria and same pontos, this way it will return the 2 products instead of 1.

  • @Maiconcarraro But isn’t it right? Ignoring a product without a rule seems incorrect to me.

  • 1

    The only rule he has is to have the majority score by category, regardless of what product it is, but I agree ctg that could have a rule that would affect the SELECT.

Browser other questions tagged mysql sql query

You are not signed in. Login or sign up in order to post.