"Daniel Dabner" <[EMAIL PROTECTED]> wrote on 03/10/2005 08:18:31 AM:
> Hi, > > I have been trying to construct an SQL query joining two tables for an image > gallery. > > I want to display all the IMAGE_CATEGORIES along with the first image in > that category from the IMAGES table. The images are ordered by an integer > field called IMAGE_ORDER. > > Here's what I have so far: > > SELECT image_categories.id, > image_categories.category_title, > images.image_filename > FROM image_categories > INNER JOIN images ON image_categories.id = > images.image_category > GROUP BY image_category > ORDER BY image_order ASC > > But this always returns the image_filename of the first record to be > inserted into the images table for that category (rather than the > image_filename of the image with the lowest image_order). If I remove the > GROUP BY clause it will order the records and the first one will be the one > I want, but then it returns ALL of the images - and I only want the first > one for each category. > > Any suggestions would be greatly appreciated. > > This is such a common question that its solution is in the MySQL manual: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html In this case you are not looking for the groupwise maximum but the groupwise minimum (the photo with the LOWEST image_order value). Just change the MAX() in the examples to MIN() and you should be able to get your query to work. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
