I have 2 tables, one for galleries and one for images. The gallery table holds the info for the gallery owner. The images table holds the image info linked by the galleryID.

I want to query the image table so that I select just the first image from each gallery. Images are uploaded at random by users so the galleryID is random in the images table depending on who uploaded and when.

SELECT *
FROM wp_ngg_gallery g INNER JOIN
(SELECT GalleryID, MIN(ID) as 'minimum'
FROM wp_ngg_gallery
GROUP BY GalleryID) x
ON g.GalleryID = x.GalleryID AND g.ID = x.minimum
ORDER BY g.GalleryID

This will return the file with the lowest ID for each gallery.
1- xyz 2- yyy 3- aaa 4- abc

It allows you to accommodate any number of Galleries. Essentially it will show the oldest picture in each gallery (assuming ID is auto incrementing). So if you wanted to show the newest image in each gallery you would just change MIN to MAX.

The added benefit of this method is that you don't have to iterate through all of the images with PHP to determine which picture you need. SQL does all the thinking for you.

can you list the fields in both tables and how they relate to each other. The code I gave you works specifically for the example you gave me. I need to see the structure of the actual tables to modify it to your needs.

It worked perfectly. I changed it so that MIN is MAX so I get the latest images and removed the stuttering AND hehe!

Nullsig, could I ask you to go through the logic of this code with me. I get INNER JOINS but have not used nested selects and group by before and don't get how you have included the extra letters like p, x & g.

Could you explain how/what each piece of code is doing. Just want to learn.

First off the letters after each table or sub query are called "aliases" it allows you to refer to the table or subquery by that letter or word instead of typing out the entire table name over and over throughout the query. It isn't required but it makes the code look cleaner and easier to read.

The sub-query that joins back to the table it queries on is due to the fact that Group By statements require every field in the select statement to be either an aggregate function or in the Group By clause. Since you needed all of the fields from the picture table and needed to choose only the (in your case) Last inserted picture for each gallery, the sub query acts as the filter for the main picture table.

This structure is actually one I use quite a bit in one of the sites I run as I always need to find the most recent/least recent/min/max value related to a 1-to-many join.