MySQL storing and querying latest software version

Question

With the following sample table, I want to create a MySQL query that returns the latest version for each of the following fictional applications (based on traditional software version numbering). I am using MySQL version 5.5.17.

I would also consider using a stored function, if a function can be created that makes a more elegant query.

app   | major | minor | patch  
------+-------+-------+--------
cat   | 2     | 15    | 0      
cat   | 2     | 15    | 1      
cat   | 2     | 2     | 0      
dog   | 1     | 0     | 1      
dog   | 1     | 7     | 2      
dog   | 3     | 0     | 0      
fish  | 2     | 2     | 5      
fish  | 2     | 3     | 1      
fish  | 2     | 11    | 0

Expected query result:

app   | major | minor | patch  
------+-------+-------+--------
cat   | 2     | 15    | 1      
dog   | 3     | 0     | 0      
fish  | 2     | 11    | 0

You can use this sql to create the table called my_table, so you can test.

CREATE TABLE IF NOT EXISTS `my_table` (
  `app` varchar(10) NOT NULL,
  `major` int(11) NOT NULL DEFAULT '0',
  `minor` int(11) NOT NULL DEFAULT '0',
  `patch` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `my_table` (`app`, `major`, `minor`, `patch`) VALUES
    ('cat', 2, 15, 1),
    ('cat', 2, 15, 0),
    ('cat', 2, 2, 0),
    ('dog', 1, 0, 1),
    ('dog', 1, 7, 2),
    ('dog', 3, 0, 0),
    ('fish', 2, 2, 5),
    ('fish', 2, 3, 1),
    ('fish', 2, 11, 0);

There are three approaches I can think of. And all of them are pretty ugly, and all of them involve subqueries.... 1) use correlated subqueries in SELECT list of a GROUP BY query, 2) use inline view to get max of canonical string concatenation of (zero padded) major_minor_patch 0002_0015_0001, and then either unpack the string representation, or join to table to get matching row, or 3) use a query that orders the rows by app, then by highest version of each app, and a trick (unsupported) with user defined values to flag the "first" row for each app. None of those is pretty. — spencer7593, Jul 13 '16 at 22:47
Do you have the option to alter the table and add another column? — Mjh, Jul 14 '16 at 14:21
Assuming that every time you insert into the table, you're providing a newer version, could you create a field called `version_count` which gets incremented (using a trigger would be the easiest) every time you add a record? You can index that field and retrieving the latest version becomes trivial. — Mjh, Jul 15 '16 at 07:35

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

If you assume that the minor version and patch never go above 1000, you can combine them into a single number major*100000 + minor*1000 + patch. Then you can apply one of the techniques at SQL Select only rows with Max Value on a Column after calculating this for each row.

SELECT m.*
FROM my_table AS m
JOIN (SELECT app, MAX(major*1000000 + minor*1000 + patch) AS maxversion
      FROM my_table
      GROUP BY app) AS m1
ON m.app = m1.app AND major*1000000 + minor*1000 + patch = maxversion

DEMO

edited May 23 '17 at 12:33

Community

1
1

answered Jul 13 '16 at 22:57

Barmar

741,623
53
500
612

This is the most concise approach.+10. I was thinking of this approach in terms of using string representation to get a canonical representation e.g. `CONCAT(LPAD( major ,10,'0'),LPAD( minor ,10,'0'), LPAD( patch ,10,0))` – spencer7593 Jul 13 '16 at 23:10
If you use a versioning scheme that includes letters, that might be the way to do it. – Barmar Jul 13 '16 at 23:15
INET_ATON will also work for a significant range of values, but it is strictly speaking 'a hack' – Strawberry Jul 13 '16 at 23:31
1

@Strawberry It's essentially doing the same thing, except using 256 instead of 1000 as the multiplier. – Barmar Jul 13 '16 at 23:32
Sure, but it's a lot less typing ;-) – Strawberry Jul 13 '16 at 23:48

spencer7593 · Answer 2 · 2016-07-14T14:16:23.753

There are three approaches I can think of. And all of them are pretty ugly, and all of them involve subqueries.... 1) use correlated subqueries in SELECT list of a GROUP BY query, 2) use inline view to get max of canonical string concatenation of (zero padded) major_minor_patch 0002_0015_0001, and then either unpack the string representation, or join to table to get matching row, or 3) use a query that orders the rows by app, then by highest version of each app, and a trick (unsupported) with user defined values to flag the "first" row for each app. None of these is pretty.

Here's a demonstration of one approach.

We start with this, to get each app:

SELECT t.app
  FROM my_table t
 GROUP BY t.app

Next step, get the highest "major" for each app, we can do something like this:

SELECT t.app
     , MAX(t.major) AS major
  FROM my_table t
 GROUP BY t.app

To get the highest minor within that major, we can make that an inline view... wrap it in parens and reference it like a table in another query

SELECT t2.app
     , t2.major
     , MAX(t2.minor) AS minor
  FROM my_table t2
  JOIN ( 
         SELECT t.app
              , MAX(t.major) AS major
           FROM my_table t
          GROUP BY t.app
       ) t1
    ON t2.app   = t1.app
   AND t2.major = t1.major
 GROUP BY t2.app, t2.major

To get the highest patch, we follow the same pattern. Using the previous query as an inline view.

SELECT t4.app
     , t4.major
     , t4.minor
     , MAX(t4.patch) AS patch
  FROM my_table t4
  JOIN ( -- query from above goes here
         SELECT t2.app
              , t2.major
              , MAX(t2.minor) AS minor
           FROM my_table t2
           JOIN ( SELECT t.app
                       , MAX(t.major) AS major
                    FROM my_table t
                   GROUP BY t.app
                ) t1
             ON t2.app   = t1.app
            AND t2.major = t1.major
          GROUP BY t2.app, t2.major 
       ) t3
    ON t4.app   = t3.app
   AND t4.major = t3.major
   AND t4.minor = t3.minor
GROUP BY t4.app, t4.major, t4.minor

That's just an example of one approach.

FOLLOWUP:

For another approach (getting a canonical representation of the version, that is, combining the values of "major", "minor" and "patch" in a single expression so that the result can be "ordered" by that expression to get the highest version), see the answer from Gordon.

MySQL storing and querying latest software version

2 Answers2