Select last entry for each type

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?

depends :slight_smile:

which dbms are you running? the ease of the answer depends on which one it is

It’s MySQL, version 5.1.x.

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?

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. :stuck_out_tongue: 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. :slight_smile:

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

Thank you. :slight_smile: 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))

Spot on, thank you! :slight_smile: I hope I’ll find the time to learn some more MySQL. :slight_smile: