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);

Answer 1

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.

Answer 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