MySQL - Pre-ordering joined content?

[Longbow]

SELECT stuff
FROM products
LEFT JOIN items ON items.product_id = products.product_id
WHERE products.category_id = '1'
GROUP BY products.name
ORDER BY products.name

Now of course I am grouping to only select 1 row from "items", but how do I pre-order the "items" table.

 

[touch]

I dont understand what you're trying to do? Could you give an example of what output you want.

 

[Longbow]

Here's the 2 tables, and the query result.



Using group, it's obviously selecting one (the first) row from "items" for each row of "products".

However, what would I do if I wanted it to arrange the "item", so instead of just grabbing the first associated row from "items", I could order it by value. Example below is ordering with the lowest value:

 

[fretted]

You could use the Min function if you want to return the lowest value in a group. Caveat, I normally write Microsoft SQL but Google says that the Min function works in mySQL too.

 

[sadbuttrue]

Use a subquery in the join and order there.

SELECT stuff
FROM products
LEFT JOIN (select * from items order by value asc) items ON items.product_id = products.product_id
WHERE products.category_id = '1'
GROUP BY products.name
ORDER BY products.name

 

[Longbow]

Thanks, the sub-query worked.