MySQl GROUP BY and ORDER BY not doing what I want

Question

I want to sort my results before grouping them, but I can't seem to get it done.

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 first order by size_position, before grouping them.

I really have no idea on how to get this done?!??

Can someone help me please??

Thanks!

Answer 1

Try to order by both columns:

... ORDER BY product.id, size_position ASC

It will sort by id first, but then by size_position.

Answer 2

This is a problem of using non-grouped columns in a grouped query. Generally, it's something to avoid. Some DB engines prevent you from doing this because it doesn't make logical sense - if I want product.id for a group, but which one is the DB engine supposed to pick because there are multiple options for a single group?

ORDER BY is only meant to work on aggregates or grouped columns when using a GROUP BY, but I'm not sure you can easily aggregate. You're looking for product.id for the row with MIN(size_position) within the group. I think a subquery to isolate this row for each group might be the only way; I can't think of an easy way off the top of my head to pull this out.

EDIT

My instinct in these cases is always to do this in two steps. I am not a fun of giant hulking queries with nested subqueries, simply because I find them unreadable. You've got a really long query on a normalised schema so I'm not going to attempt to break down query here, I'm likely to make a mistake.

Query 1 would push product_number, color_number and MIN(size_position) (grouping by product_number, color_number) into a temp table.

Query 2 would then retrieve the product_id for each product_number, color_number, size_position in the temp table. This way you can do a join directly against the min size position.

The negative performance of using temp tables is overhyped in many cases (not all, but certainly for 99% of the queries I've conducted in the last 10 years in trading apps).

There is another possibility involving a subquery self-join but with the schema so heavily normalised I think that wouldn't be particularly advantageous to implement.

Answer 3

If you group by product_number, color_number, the values returned for column id are indeterminate (because they are not the same for each product_number,color_number pair). See here for a detailed explanation.

So, you say "I want to sort my results before grouping them", can you say why you want to group them? Exactly what do you want to get from it.

EDIT: after 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 the product_number, but not the id.