SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    NJ
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting unique records

    I've got two tables used to store image files I'm trying to query out pairs of images based on the aspect ratio

    Given that the following query :
    Code:
    SELECT
        media.title as title,
        media_file.filename as filename,
        thumb_file.filename as thumbnail,
        thumb_file.size_label,
        thumb_file.aspect
    FROM
        media
    LEFT JOIN media_file ON
        media_file.media_id = media.media_id AND
        media_file.size_label = "full"
    LEFT JOIN media_file as thumb_file ON
        thumb_file.media_id = media.media_id AND
        thumb_file.size_label = "mid"
    Produces the following dataset:
    Code:
    title        | filename     | thumbnail    | size_label | aspect
    -------------+--------------+--------------+------------+-------
    First Record | BT-1_s-f.jpg | BT-1_s-m.jpg | mid        | 1.0000
    First Record | BT-1_s-f.jpg | BT-1_w-m.jpg | mid        | 0.5625
    Secd Record  | BT-2_s-f.jpg | BT-2_s-m.jpg | mid        | 1.0000
    Secd Record  | BT-2_s-f.jpg | BT-2_w-m.jpg | mid        | 0.5625
    How do I limit the results to return image pairs with the highest aspect ratio. For this dataset I'm looking for :
    Code:
    title        | filename     | thumbnail    | size_label | aspect
    -------------+--------------+--------------+------------+-------
    First Record | BT-1_s-f.jpg | BT-1_s-m.jpg | mid        | 1.0000
    Secd Record  | BT-2_s-f.jpg | BT-2_s-m.jpg | mid        | 1.0000
    The trick is that the highest aspect will not always be 1. I tried tacking group by's to the end of the query but I know I'm missing something here. I'm not sure where to put a conditional based on the aspect field in.

    Code:
    GROUP BY media_file.filename
    I'm sure I'm missing something obvious but I'm quicly learning that my SQL skills are sorely lacking.

    Thanks,
    Chris.

  2. #2
    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 media.title as title
         , media_file.filename as filename
         , thumb_file.filename as thumbnail
         , thumb_file.size_label
         , thumb_file.aspect
      FROM media
    LEFT 
      JOIN media_file 
        ON media_file.media_id = media.media_id 
       AND media_file.size_label = 'full'
    LEFT 
      JOIN media_file as thumb_file 
        ON thumb_file.media_id = media.media_id 
       AND thumb_file.size_label = 'mid'
       AND thumb.file.aspect
         = ( select max(aspect)
               from media_file
              where size_label = 'mid'
                and media_id = media.media_id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    NJ
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sub selects

    Any chance of avoiding sub-queries or is this just another hint that it's finally time to upgrade services?

  4. #4
    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)
    4.1 is the production version prior to the current production version

    if you're on a version that's even older than that, then yes, it's time
    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
  •