Date: 08/27/06 (MySQL Communtiy)    Keywords: no keywords

I've got categories, and products in categories. (generalizing a bit). The products have a certain order within each category. Each product has an image. I need to list all of the categories, showing the image for the product with the lowest sort order in each category.

Imagine:
create table categories (id int auto_increment not null primary key, name varchar(32));
create table products (id int auto_increment not null primary key, category_id int references categories(id), name varchar(32), image varchar(32), sort int);

categories: (1,'Watches'),(2,'Surfboards');
products: (1,1,'Alpha','alpha.jpg',2), (2,1,'Beta','beta.jpg',0), (3,2,'Surboard','surf.jpg',3), (4,2,'Surfboard2','fwibble.jpg',4);

I want a query to return:

1, Watches, Beta, beta.jpg
2, Surfboards, Surfboard, surf.jpg

I feel like I'm missing something obvious, but I'm blanking. I thought I could GROUP BY category_id HAVING min(products.sort) = products.sort, but no luck. ;)

Source: http://community.livejournal.com/mysql/101787.html