"Daniel Dabner" <daniel.dabner@stripped> wrote on 03/10/2005 08:18:31 AM:
> I have been trying to construct an SQL query joining two tables for an
> 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
> field called IMAGE_ORDER.
> Here's what I have so far:
> SELECT image_categories.id,
> FROM image_categories
> INNER JOIN images ON image_categories.id =
> 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
> GROUP BY clause it will order the records and the first one will be the
> I want, but then it returns ALL of the images - and I only want the
> one for each category.
> Any suggestions would be greatly appreciated.
This is such a common question that its solution is in the MySQL manual:
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.
Unimin Corporation - Spruce Pine