Getting latest record from multiple MySQL Tables based on 2 criteria

Question

I have a database that contains serval tables that hold information about the packages we have deployed into our various environments. This database is then interrogated to give an overall picture of the latest versions of the software that are installed which is displayed in a table on a web page.

What I am trying to do, and am struggling with is get the latest version of each package that is installed in each environment. I started to use the information in this post:

Retrieving the last record in each group

Which helped a lot with another aspect of the software, but where I am falling down is because I have more than one criteria on which to select. The relevant parts of the database are as follows

Package Versions table

package_versions

id
server_name_id
package_name_id
version

Package Name table

package_names

id
name

Server Name table

server_names

id
name
environment_name_id

Finally the Environment Names table

environment_names

id
name

This structure allows me to hold a history of the packages and the versions that are deployed into the various environments.

Now what I need to do it get the latest versions of the all the packages that are deployed in each environment. As I said I have been successful with the solution in the post above when I am playing with one table and just two criteria, but here it is 4 tables and 2 criteria.

My attempts either give me back every version that has been installed in the environment:

SELECT  environment_names.name as environment,
        package_names.display as package,
        package_names.name as pkg_name,
        package_versions.version as package_version
FROM    package_versions INNER JOIN package_names ON package_names.id = package_versions.package_name_id
                         INNER JOIN server_names ON server_names.id = package_versions.server_name_id
                         INNER JOIN environment_names ON environment_names.id = server_names.environment_name_id
                         LEFT JOIN package_versions pv ON (package_versions.package_name_id = pv.package_name_id 
                                                           AND package_versions.id < pv.id)
AND     pv.id IS NULL
ORDER BY environment_names.name, package_names.name

It is not possible to just use Max on the version number as it is generated by Maven so it is in the format x.x.x and can have SNAPSHOT in it.

Other attempts just provide me with the latest version installed anywhere.

I think I need to add something in the LEFT join part but I am really struggling to work out what. Any help is gratefully recieved.

Thanks very much,

Russell

if its major/minor/release[-SNAPSHOT] versioning, you will need a much more complicated sort algorithm. otherwise 1.10 comes before 1.2... Can you not break this out into code instead? — Lucas, May 03 '13 at 14:09
Lucas, yeah that is what I am trying to avoid. I want to get all the data back in one query from the database rather than doing loads of smaller ones. I had thought I might be able to use the LEFT JOIN trick but it maybe that is just too much. — Russell Seymour, May 03 '13 at 14:44
You could attempt to write a function to split the version apart and compare each segment, but as mysql does not have a split function, this can be very difficult. Anything can be accomplished with enough code, but you will find this ends up being MUCH simpler when you break it out into code... — Lucas, May 03 '13 at 16:06

score 1 · Answer 1 · answered May 03 '13 at 18:38

What you need is a ranking function which MySQL currently does not provide. There is a way to simulate it. In addition, you need to split the version values so that you can sort them.

Select envName, pkgName, version
From (
      Select envName, pkgName, version
        , @rnk := if(@env=envId && @pkg=package_name_id, if(@pkgVer=verId, @rnk, @rnk + 1), 1) As rnk
        , @pkgVer := verId
        , @env := envId
        , @pkg := package_name_id
      From (
            Select env.id As envId
              , env.name As envName
              , ver.package_name_id
              , pkg.name As pkgName
              , ver.id As verId
              , ver.version
              , Cast(Substring_Index( ver.version , '.', 1 ) As signed) As Major
              , Cast(Substring_Index(Substring_Index( ver.version , '.', 2 ),'.',-1) As signed) As Minor
              , Cast(Substring_Index(Substring_Index( ver.version , '.', -2 ),'.',1) As signed) As Build
              , Cast(Substring_Index( ver.version , '.', -1 ) As signed) As Revision
            From package_versions As ver
              Join package_names As pkg
                On pkg.id = ver.package_name_id
              Join server_names As sys
                  On sys.id = ver.server_name_id
              Join environment_names As env
                  On env.id = sys.environment_name_id
              Cross Join ( Select @rnk := 0
                              , @ver := ''
                              , @env := 0
                              , @pkg := 0 ) As Z
          ) As envPackages
      Order By envName, pkgName, Major Desc, Minor Desc, Build Desc, Revision Desc
      ) As rnkedPackages
Where rnk = 1
Order By envName, pkgName, rnk

SQL Fiddle

score 0 · Answer 2 · answered May 03 '13 at 14:09

0

SELECT environment_names.name as environment,
       package_names.display as package,
       package_names.name as pkg_name,
       MAX(package_versions.version) as package_version
FROM package_versions 
INNER JOIN package_names ON package_names.id = package_versions.package_name_id                             
INNER JOIN server_names ON server_names.id = package_versions.server_name_id                             
INNER JOIN environment_names ON environment_names.id = server_names.environment_name_id
GROUP BY environment_names.name,
         package_names.display,
         package_names.name
ORDER BY environment_names.name, package_names.name

answered May 03 '13 at 14:09

Nagasaki

60
2
16

The OP says _It is not possible to just use Max on the version number as it is generated by Maven so it is in the format x.x.x and can have SNAPSHOT in it._ – Lucas May 03 '13 at 14:10
if the id from the table package_versions is autoincrement you can also get the max(id) to find the latest version of a package, and have a subrequest with it to get the version number – Nagasaki May 03 '13 at 14:14

score 0 · Accepted Answer · answered May 07 '13 at 10:31

All,

Thanks very much for all the suggestions, they all helped.

None of the MAX() functions I put around the package_versions.id or package_versions.deploy_time worked properly. I seemed to get the latest version id for example, but the actual version number did not match that record.

I worked out this was due to the two tables I needed to use to get the environment name. I have now added a relationship to the PackageVersions table so that I do not have to use the server table as an intermediatory. So now the tables look like:

Package Versions Table

package_versions

id
package_name_id
environment_name_id
version

(The server_name_id is still in there so that I can use it give me more detailed information about an environment).

Package Names Table

package_names

id
name

Environment Names Table

environment_names

id
name

Now my SQL query looks like:

SELECT  package_versions.id,
        environment_names.name,
        package_names.name,
        package_versions.version,
        max(package_versions.id)
FROM    package_versions INNER JOIN environment_names ON package_versions.environment_name_id = environment_names.id
                         INNER JOIN package_names ON package_names.id = package_versions.package_name_id
GROUP BY environment_names.id, package_names.name                        
ORDER BY environment_names.name, package_names.name;

This is now giving me the information I need and matches the system I am replacing with this new code.

Thanks to Lucas and Nagaski for their suggestions.

Russell

Getting latest record from multiple MySQL Tables based on 2 criteria

3 Answers3