Databases
StackOverflow2013
Postsdbo
POMySQl GROUP BY and ORDER BY not doing what I want
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQl GROUP BY and ORDER BY not doing what I want
    text
    copied!<p>I want to sort my results before grouping them, but I can't seem to get it done.</p> <p>I have this query:</p> <pre> 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 </pre> <p>The result is the following table:</p> <pre> 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 </pre> <p>If I group by product_number, color_number I get id <b>365</b> and 371.<br> But I want to get id <b>369</b> and 371, because I want the results to first order by size_position, before grouping them. <br><br> I really have no idea on how to get this done?!??<br><br> Can someone help me please??<br><br> Thanks!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload