MySQL select: maximum 3 photos from the same album

Question

Hello there! I have table with next structure:

`id` (int), `album_id` (int), `photo_id` (int), `date_added` (int)

The task is next: I must select 100 photos from this table, ordered by date_added and intricacy in that I could select maximum 3 picture from the same album. That is I could select one, two or three photos from one album in this selection. How I could perform query this more optimized? What kind of mysql functions I must use?

Answer 1

Houw about something like

SELECT  *
FROM    album_photos ap
WHERE   ap.id IN    (
                        SELECT  id 
                        FROM    album_photos 
                        WHERE   album_id = ap.album_id 
                        ORDER BY    date_added DESC 
                        LIMIT 3
                    )
LIMIT 100