obtaining the first image in each album

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davidklonski
Forum Contributor
Posts: 128
Joined: Mon Mar 22, 2004 4:55 pm

obtaining the first image in each album

Post by davidklonski »

Hello

Assuming I have the following table:

CREATE TABLE photo_in_album (
Album_ID INT UNSIGNED NOT NULL,
Image_ID INT UNSIGNED NOT NULL,
) ENGINE=MYISAM DEFAULT CHARSET=latin1;

which provides a many-to-many relationship between photos and albums.

Is it possible to extract each album and the first photo in that album?
Here is an example:
Album Photo
1 1042
1 1046
1 1439
2 158
2 1582
2 1585
3 4354
3 96
3 99

The query will return:
(1, 1042)
(2, 158)
(3, 4354)

thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I think you could use DISTINCT for that.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

select Album, min(Photo) from photo_in_album group by Album
Post Reply