List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 10 2005 2:10pm
Subject:Re: Retrieving only the first record in a grouped query
View as plain text  
"Daniel Dabner" <daniel.dabner@stripped> wrote on 03/10/2005 08:18:31 AM:

> Hi,
> I have been trying to construct an SQL query joining two tables for an 
> 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 
> field called IMAGE_ORDER.
> Here's what I have so far:
>    SELECT,
>          image_categories.category_title,
>          images.image_filename
>    FROM      image_categories
>    INNER JOIN   images ON =
> 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 
> 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.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Retrieving only the first record in a grouped queryDaniel Dabner10 Mar
  • Re: Retrieving only the first record in a grouped querySGreen10 Mar