StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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?</p> <p>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.</p> <p><strong>EDIT</strong></p> <p>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.</p> <p>Query 1 would push product_number, color_number and MIN(size_position) (grouping by product_number, color_number) into a temp table.</p> <p>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.</p> <p>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).</p> <p>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.</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