SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast kneekoo's Avatar
    Join Date
    Dec 2010
    Location
    Bucharest, Romania
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Select last entry for each type

    mysql_select_latest_by_type.png
    I want to select the releases with the IDs 8, 17, 20. They are the latest (by version and release_expires) for their release_type. Is there an easy way to select them in one query?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by kneekoo View Post
    Is there an easy way to select them in one query?
    depends

    which dbms are you running? the ease of the answer depends on which one it is
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast kneekoo's Avatar
    Join Date
    Dec 2010
    Location
    Bucharest, Romania
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's MySQL, version 5.1.x.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    thanks

    could you explain "latest (by version and release_expires) for their release_type"

    why does it take two column values to figure out the latest? wouldn't version alone suffice?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast kneekoo's Avatar
    Join Date
    Dec 2010
    Location
    Bucharest, Romania
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well... that's rather an ambiguous choice of words. Before I created this thread there wasn't a version number for the Debian (ID 8) row, but only a "-". It occurred to me that in the future there might be other releases with the type "2", so I gave it a version number and made another screenshot but I didn't think to revise my post. As it is now, it looks safe to use only the version number to check for the latest of each type.

    I'm in the process of redesigning our outdated download wizard for the Linux Mint releases and... of course there are many ways to skin a cat. My first thought was to put everything in an interface, which one can use to select option by option what kind of disk image he/she wants. Based on all previously selected options, the following ones get updated with JavaScript and after you finish choosing your particular needs you click the download button and get the ISO.

    However, being stuck with this query allowed me to rethink/refine the whole wizard idea. With the regular users in mind, I thought it might be easier for them to present the differences between the Mint releases: regular, long-term and perpetual. I would have to design a 3-tabbed interface that presents in as few words possible what each release type means, which would allow me to present the release details a lot easier to choose. Now making it easier for the user would still require this query I came here to ask for, but then another idea popped up.

    Considering I would have to execute that not-so-simple query each time I load the first page of the website, I figured that I might as well use my admin back-end to store the latest IDs in the CMS settings directly - like a cache. After all, they will always be just a few bytes: "123, 132, 138". Then I could simply SELECT * FROM minteditions WHERE release_id IN (123, 132, 138). Right?

    The trade-off is I would "bloat" each web page instance with this extra setting but on the other side I could as well use those IDs in other sections of the website whenever I get some brilliant idea. :P What do you think?

    I'm still interested in the query I came here for, as it would give me an extra option for the download wizard, so I'll keep an eye here. Thanks for taking time to answer me.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by kneekoo View Post
    As it is now, it looks safe to use only the version number to check for the latest of each type.
    Code:
    SELECT t.release_id
         , t.release_codename
         , t.release_version
         , t.release_base
         , t.release_type
         , t.release_expires
      FROM ( SELECT release_type
                  , MAX(release_version) AS latest
               FROM minteditions 
             GROUP
                 BY release_type ) AS m
    INNER
      JOIN minteditions AS t
        ON t.release_type = m.release_type             
       AND t.release_version = m.latest
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast kneekoo's Avatar
    Join Date
    Dec 2010
    Location
    Bucharest, Romania
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you. It looks a bit complicated. It selects 8, 12, 13, so I'll look around and see if I can figure it out.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,018
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by kneekoo View Post
    Thank you. It looks a bit complicated. It selects 8, 12, 13, so I'll look around and see if I can figure it out.
    oh, it's because of the MAX on the VARCHAR values

    replace MAX(release_version) with MAX(CAST(release_version AS DECIMAL))
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast kneekoo's Avatar
    Join Date
    Dec 2010
    Location
    Bucharest, Romania
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Spot on, thank you! I hope I'll find the time to learn some more MySQL.


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
  •