What is there? There is a table of this type

+----+--------------+-------+--------------+ | id | product_name | count | warehouse_id | +----+--------------+-------+--------------+ | 1 | Puperproduct | 10 | 1 | | 2 | Puperproduct | 15 | 2 | | 3 | Puperproduct | 12 | 3 | +----+--------------+-------+--------------+

What you need to get? You need to get the max and min amount of goods from their warehouses.

 +-----+---------------+-----------+------------------+-----------+------------------+ | id | product_name | min_count | min_warehouse_id | max_count | max_warehouse_id | +-----+---------------+-----------+------------------+-----------+------------------+ | 1 | Puperproduct | 10 | 1 | 15 | 2 | +-----+---------------+-----------+------------------+-----------+------------------+

What did I do? Happened:

 select id , product_name , min(count) as min_count , max(count) as max_count from table group by product_name +----+--------------+-----------+-----------+ | id | product_name | min_count | max_count | +----+--------------+-----------+-----------+ | 1 | Puperproduct | 10 | 15 | +----+--------------+-----------+-----------+

How do i get warehouses? The number of entries is about 800K. Mysql 5.5

  • Well, add MIN(warehouse_id) as min_warehouse_id, MAX(warehouse_id) as max_warehouse_id do you need it? - Alexey Shimansky
  • one
    no, judging by the example, you need a warehouse ID where count == max_count - zRrr
  • @zRrr This is the problem. MAX (warehouse_id) I just get max warehouse id. And you need a warehouse where goods are minimal and maximal. - Alma Z

5 answers 5

We put in one count line supplemented with, say, up to 10 characters and the corresponding warehouse_id , we take min / max from this line and then we cut out the warehouse back and convert it to a numerical form:

 select id, product_name, max(count) as max_count, min(count) as min_count, substr(min(concat(lpad(count,10,'0'),warehouse_id)),11)+0 min_warehouse_id, substr(max(concat(lpad(count,10,'0'),warehouse_id)),11)+0 max_warehouse_id from table group by product_name
  • sqlfiddle.com/#!9/048a1/2 - your is not true - splash58
  • @ splash58 Well, by chance one max instead of min wrote ... sqlfiddle.com/#!9/048a1/5 - Mike
  • Well I do not mind :) - splash58
  • But what is the thread that does not hurt? sqlfiddle.com/#!9/170e/4 - splash58
  • one
    @AlmaZ I do not see much difference, the only question is what to sew up the correct maximum length. And if the sorting is initially on the line, then use rpad instead of lpad and add spaces - Mike

You can do the following:

 SELECT gr.id AS id , gr.product_name AS product_name , gr.min_count AS min_count , min.warehouse_id AS min_warehouse_id , gr.max_count AS max_count , max.warehouse_id AS max_warehouse_id FROM (SELECT id , product_name , min(count) AS min_count , max(count) AS max_count FROM table AS main GROUP BY product_name) AS gr LEFT JOIN table AS min ON gr.product_name = min.product_name AND gr.min_count = min.count LEFT JOIN table AS max ON gr.product_name = max.product_name AND gr.max_count = max.count;

However, it is better to see with the analyzer on the product base whether several requests will be cheaper.

    And if you just use select * from table order by count desc ? Then the "top" will be max number, and the "bottom" - min.

    • And how will this help? It is necessary to get the result in one line. - pegoopik

    It may be idiotic and not very optimal (However, this was not the case), but it seems to work:

     SELECT tmp.product_name, tmp.min_count, t2.warehouse_id as min_warehouse_id, tmp.max_count, t3.warehouse_id as max_warehouse_id FROM ( SELECT t1.id , t1.product_name , MIN(COUNT) AS min_count , MAX(COUNT) AS max_count FROM test1 t1 GROUP BY t1.product_name) tmp JOIN test1 t2 ON tmp.product_name = t2.product_name AND tmp.min_count = t2.count JOIN test1 t3 ON tmp.product_name = t3.product_name AND tmp.max_count = t3.count

      Why no one remembers the variables in MySQL? The option from Mike will probably work faster, but still offer an alternative:

       SELECT id, product_name, count, warehouse_id, CASE WHEN count = @I THEN 'MIN' ELSE 'MAX' END row_type FROM( SELECT * ,@I := CASE WHEN @I<count AND @I>=0 THEN @I ELSE count END min_count ,@J := CASE WHEN @J>count AND @J>=0 THEN @J ELSE count END max_count FROM thetable, (SELECT @I := -1, @J:= -1)T )T WHERE count IN (@I, @J)

      All warehouses with the maximum and minimum value of the count field are displayed here. You can see the result here:

      link to http://sqlfiddle.com/