kneekoo — 2013-12-28T21:05:28-05:00 — #1
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?
r937 — 2013-12-28T21:46:19-05:00 — #2
which dbms are you running? the ease of the answer depends on which one it is
kneekoo — 2013-12-29T03:57:43-05:00 — #3
It's MySQL, version 5.1.x.
r937 — 2013-12-29T08:00:27-05:00 — #4
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?
kneekoo — 2013-12-29T11:18:19-05:00 — #5
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.
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. 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.
r937 — 2013-12-29T15:36:36-05:00 — #6
FROM ( SELECT release_type
, MAX(release_version) AS latest
BY release_type ) AS m
JOIN minteditions AS t
ON t.release_type = m.release_type
AND t.release_version = m.latest
kneekoo — 2013-12-29T16:35:58-05:00 — #7
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.
r937 — 2013-12-29T16:52:45-05:00 — #8
oh, it's because of the MAX on the VARCHAR values
replace MAX(release_version) with MAX(CAST(release_version AS DECIMAL))
kneekoo — 2013-12-29T19:22:46-05:00 — #9
Spot on, thank you! I hope I'll find the time to learn some more MySQL.