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 
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
Thread
Retrieving only the first record in a grouped queryDaniel Dabner10 Mar
  • Re: Retrieving only the first record in a grouped querySGreen10 Mar