Need a bit of help with some MySQL

[Mattus]

Hi guys,

As part of a web application I'm creating, I have a 'timeline' which contains a series of images which I want to retrieve from a database in chronological order. Some of these images are grouped into 'albums', and when this is the case, I only want the query to fetch the first image from that album. To do this, I've been using a GROUP BY clause so that each album only appears once.

Here is my current SQL query:

$sql = "SELECT * FROM Images WHERE timelineID ='" . $image['timelineID'] . "' GROUP BY albumID ORDER BY ImageDate ASC"

This almost works. It retrieves the images and groups the albums together so that each album only appears once in the listing.

The only problem is that when the GROUP BY clause consolidates all the images from an album into one record, the image returned is the last image from that album, in chronological order.

Does anyone know how to modify the query so it returns the first result from the group?

(edit) It would probably work if I could incorporate another ORDER BY clause. I could order the images by DESC, then group them. This would ensure that each album was represented by the image I wanted, because the last image returned for each album would actually be the first one. Then I could order again by ASC to put the results back into the right order. But I don't know how to code that query?

 

[Pho]

$sql = sprintf("SELECT albumID, timelineID, min(imageDate) FROM Images WHERE timelineID = %d GROUP BY albumID, timelineID", $image['timelineID'] );

Maybe?

 

[Mattus]

Thanks for your help.. sadly that doesn't quite work I think it doesn't work because different albums can overlap chronologically.

I'll try and explain a bit better. Imagine I have the following images:

I want to return one result for each album. I want this result to be the earliest image in the album, and I want the albums to be sorted into chronological order according to this first image. So from the above data, I want a query that gives this:

The problem is that my GROUP BY statement picks out the last (latest) image from each album rather than the first. So the output I'm actually getting is this:

Anyone got any ideas?

 

[Pho]

SELECT *
FROM   images imgs
WHERE  imageid IN (SELECT   TOP 1 imageid
                   FROM     images
                   WHERE    albumid = imgs.albumid
                   ORDER BY imagedate) 
ORDER BY imageDate

Tested in MSSQL - seems to work from your dataset .

Mind, I've always read using IN is ugly but nevermind .