There is such a request, in two versions:

SELECT order_id, x as x1, y as y1, MAX(x)+1 as x2, MAX(y)+1 as y2 FROM `all_items` where order_id>0 group by order_id SELECT order_id, MIN(x) as x1, MIN(y) as y1, MAX(x)+1 as x2, MAX(y)+1 as y2 FROM `all_items` where order_id>0 group by order_id

both work the same way, runtime one to one to ms.
Judging by the documentation, only the second request is correct, in this case I can say lucky that x1 and y1 need just the minimal ones from the set, and if you needed something else, how to pull out the remaining columns from the selected rows?

    2 answers 2

    According to classical standards, in the list of fields using aggregate functions, you can specify only aggregate functions or fields that are grouped. MySQL is more liberal in this respect and allows you to specify any additional fields. But the values ​​of these fields (if they are different within the grouping) will be chosen arbitrary

    • those. The second request except that it fully complies with the documentation, but is also the only one allowed? how, then, get what you need? I need the first and last fields, in my case they just fit into the aggregation, and if they didn’t fit, it was necessary to pick up the first line and the last line from the group? - Sergey V.
    • SELECT * FROM mytable WHERE ROW (col1, col2) = (SELECT col1, MIN (col2) FROM mytable GROUP BY col1) UNION SELECT * FROM mytable WHERE ROW (col1, col2) = (SELECT col1, MAX (col2) FROM mytable GROUP BY col1) - Anton Shchyrov
    • one
      Is to blame. Instead of ROW (..) = you need ROW (..) IN - Anton Shchyrov
    • one
      HAVING MAX (x) - MIN (X)> 50 - Anton Shchyrov
    • one
      And yes, ROW () is not friendly with indexes and using ROW () generates a full table scan. You can try to replace it with JOIN - Anton Shchyrov

    Check out this article on grouping - http://sqlinfo.ru/articles/info/18.html there are 5 ways to correctly select data for which the use of aggregate functions is not suitable. Constructions can be written more, but, IMHO, they will be another form of writing the listed methods.