Select top-sellers in each category

0

Hello

I have a table with id, id_category, product name and quantity sold. I want to list the first products sold in each category, even if the second or third product in the "A" category, for example, sold more than the first in the "B" category, I want the first of each category to appear !!!! < p>     

asked by anonymous 25.02.2018 / 18:23

1 answer

1
  

I want the first of each category to appear

Answering the above question ....

Considering that your table is similar to the table described below:

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(11)          | NO   | PRI | NULL    | auto_increment |
| id_categoria | int(10) unsigned | NO   |     | NULL    |                |
| nome         | varchar(32)      | NO   |     | NULL    |                |
| qnt_vendida  | int(10) unsigned | YES  |     | 0       |                |
+--------------+------------------+------+-----+---------+----------------+

You can use the following query to list only the first product in each category

SET sql_mode='';
SELECT id_categoria, nome FROM produtos GROUP BY id_categoria;

Or even

SELECT id_categoria, ANY_VALUE(nome) FROM produtos GROUP BY id_categoria;

The GROUP BY will group all the identical column values "id_category", that is, it will return only the data without repeating them and with that we can capture the values.

SET sql_mode=''; and ANY_VALUE serve to avoid error ONLY_FULL_GROUP_BY . This error is generated when you use a non-aggregated value in a query that has GROUP BY .

Demo

    
25.02.2018 / 18:59