I'm not 100% sure what result set you are expecting, but the following query will return a single row with priority 1:
SELECT article_id, photo_name, priority FROM articles INNER JOIN (SELECT * FROM article_photos ORDER BY photo_article_id,priority) photos ON article_id = photos.photo_article_id GROUP BY article_id; Thanks, Nathan. http://www.kennedytechnology.com Telerobotics project: http://control.kennedytechnology.com Interactive xmas lights project: http://www.pacificlights.co.nz -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Chris Sent: Thursday, 11 February 2010 10:22 a.m. To: NZ PHP Users Group Subject: [phpug] Order By not working with Group By Hi guys, I am using MySql 5.0.89-community I have two database tables 'articles' and 'article_photos' where each article has multiple photos and each photo has an priority field associated with it. I am trying to get a list of articles with a photo for each one that has the priority of 1 CREATE TABLE articles ( article_id int(11) unsigned NOT NULL auto_increment, article_title tinytext NOT NULL, KEY article_id (article_id) ); INSERT INTO articles VALUES (1, 'News Article 1'); CREATE TABLE article_photos ( photo_id int(11) unsigned NOT NULL auto_increment, photo_article_id int(11) unsigned NOT NULL default '0', photo_name tinytext NOT NULL, priority tinyint(4) unsigned NOT NULL default '1', KEY photo_id (photo_id) ); INSERT INTO article_photos VALUES (1, 1, 'Photo 1', 2); INSERT INTO article_photos VALUES (2, 1, 'Photo 2', 3); INSERT INTO article_photos VALUES (3, 1, 'Photo 3', 1); The query below should return the article with a photo that has the priority set to 1 but it does not. Does anyone know a workaround for this? SELECT article_id, photo_name, priority FROM articles INNER JOIN (SELECT * FROM article_photos GROUP BY photo_article_id ORDER BY priority ASC) photos ON article_id = photos.photo_article_id -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
