Take away the GROUP BY thing. And after that you just check if the rows are in the order that you want. The upper row would be the one that GROUP BY will take. Are you sure that you want the "lowest" value in the "item_update"? I would like to have the highest value.
If you want the latest date, then you do ORDER BY i.item_updated DESC GROUP BY i.product_id -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kim Christensen Sent: Monday, June 12, 2006 9:15 PM To: Peter Lauri Cc: MySQL List Subject: Re: Limiting results from joins On 6/12/06, Peter Lauri <[EMAIL PROTECTED]> wrote: > -------------------------- > > Here's what it looks like right now: > > SELECT * FROM products p > INNER JOIN manufactors m ON m.manufactor_id = p.manufactor_id > INNER JOIN items i ON i.product_id = p.product_id > > The problem is, that each entry in "products" may occur more than once > in "items", and they are identified by "product_id" in both tables. > How do I filter out the results from the last INNER JOIN by certain > criterias? I want the INNER JOIN to only return the row from items > which has the lowest value in the column "item_updated". > > -------------------------- > > Just add: > > ORDER BY i.item_updated GROUP BY i.product_id > > (assuming that item_updated and product_id are in table i) That gives me the result that I'm after, the only problem seems to be that the sorting is made before the matching - on all the rows (huge execution time) - how can I prevent this? MVH -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
