SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    obtaining the first image in each album

    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

  2. #2
    SitePoint Zealot Overunner's Avatar
    Join Date
    Mar 2004
    Location
    Sweden
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know if it is possible to do that in one query. The only solution I can think at the moment is this:

    SELECT DISTINCT Album_ID FROM photo_in_album

    And after you've retrieved all your album ids you could:

    SELECT Image_ID FROM photo_in_album WHERE Album_ID = 'one of your album ids' (LIMIT 1)

    Of course you need to execute the query N times, where N is the number of albums you have.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select Album_ID 
         , min(Image_ID) as first_image
      from photo_in_album
    group
        by Album_ID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •