MySQl GROUP BY and ORDER BY do not do what I want

Question

I want to sort the results before grouping them, but I cannot do this.

I have this query:

  SELECT DISTINCT
  product.id,
  product_number_value.att_value AS product_number,
  color_number_value.option_code AS color_number,
  size_value.option_code AS size_code,
  size_value.option_position AS size_position
 FROM
  product
 INNER JOIN product_attribute_relational AS product_number_relational ON product_number_relational.product_id = product.id
 INNER JOIN product_att_varchar AS product_number_value ON product_number_relational.product_att_id = product_number_value.product_att_id
 INNER JOIN product_attribute_relational AS color_number_relational ON color_number_relational.product_id = product.id
 INNER JOIN product_att_select AS color_number_select ON color_number_relational.product_att_id = color_number_select.product_att_id
 INNER JOIN attribute_option_value AS color_number_value ON color_number_select.att_value = color_number_value.option_id
 INNER JOIN product_attribute_relational AS size_relational ON product.id = size_relational.product_id
 INNER JOIN product_att_select AS size_select ON size_relational.product_att_id = size_select.product_att_id
 INNER JOIN attribute_option_value AS size_value ON size_select.att_value = size_value.option_id
 WHERE
  product_number_relational.att_id = 1 AND
  color_number_relational.att_id = 2 AND
  size_relational.att_id = 3 AND
  product.id IN (365, 366, 367, 368, 369, 371, 372, 373, 374)
 ORDER BY
  product.id ASC

The result is the following table:

  id product_number color_number size_code size_position
 365 F23740 311 S 24
 366 F23740 311 M 25
 367 F23740 311 L 26
 368 F23740 311 XL 27
 369 F23740 311 XS 23
 371 F23745 213 S 24
 372 F23745 213 M 25
 373 F23745 213 L 26
 374 F23745 213 XL 27

If I group by product number, color_number, I get id 365 and 371.
But I want to get id 369 and 371 because I want the results to be sorted by size_position first before grouping them.

I really don't know how to do this? Can someone help me?

Thanks!

Answer 1

This is the problem of using non-group columns in a grouped query. In fact, this can be avoided. Some DB mechanisms do not allow you to do this because it is not logical: if I want product.id for a group, but which one should use the DB mechanism because there are several options for one group?

ORDER BY is only for working with aggregates or grouped columns when using GROUP BY, but I'm not sure if you can easily aggregate. You are looking for product.id for a row with MIN (size_position) inside the group. I think that a subquery to isolate this row for each group may be the only way; I can't think of an easy way from my head to get it out.

EDIT

My instinct in these cases should always do this in two stages. I don’t really like gigantic clumsy queries with nested subqueries, simply because I find them unreadable. You have a very long query for a normalized schema, so I'm not going to try to break the query here, I will probably make a mistake.

Query 1 will call product_number, color_number and MIN (size_position) (grouping by product_number, color_number) in the temp table.

Query 2 will then retrieve the product_id for each product_number, color_number, size_position in the temp table. Thus, you can directly join the position of the minimum size.

The negative performance of using temporary tables is in many cases too high (not for everyone, but, of course, for 99% of the requests I have made over the past 10 years in trading applications).

There is another possibility related to the self-subordination of the subquery, but with such a strongly normalized scheme, I think that it would not be particularly beneficial to implement it.

Answer 2

If you group by object_number color_number, the values ​​returned for the column identifier are undefined (since they are not the same for each pair of product_number, color_number). See here for more details.

So you say, “I want to sort my results before grouping,” can you say why you want to group them? What you want to get from him.

EDIT: after the comment:

SELECT DISTINCT product_number_value.att_value AS product_number, color_number_value.option_code AS color_number, min(size_value.option_position) AS sizePosition from ... group by product_number, color_number order by sizePosition 

You will have a product_number number, but not an id.

Answer 3

Try sorting by both columns:

 ... ORDER BY product.id, size_position ASC 

At first it will be sorted by id, but then by size_position.