CODEWITHSUNDEEP
mysqlmaxinner-join
Rich
Rich

Reputation: 15767

Using max() to get the latest rows in a table, joined on another table

I have the following schema which I am sadly not allowed to change:

+------------+ 
| vHistory v | 
+------------+       +---------------+
| id         |       | instance i    |         +----------------------+
| component  |       +---------------+         | environment e        |
| instance   |* === 1| id            |         +----------------------+
| version    |       | environment   |* ===== 1| idEnv                |
| date       |       +---------------+         | environment          |
+------------+                                 +----------------------+

(vHistory lists installation dates of componants on middleware; instance lists the an instance of a middleware server (eg "Tomcat 6.0.14 on machine "production-server-78"); environment means production/staging/etc)

I would like to produce a list for a particular v.component with the following columns:

v.id, v.version, e.environment, v.date

showing the latest installation of the chosen component in each environment. I have tried the following query:

SELECT  MAX(v.id), 
        e.environment, 
        v.version, 
        MAX(nv.date)
FROM vHistory v
  INNER JOIN instance i
    ON v.instance = i.id
  INNER JOIN environment e
    ON i.environment = e.idEnv
WHERE v.component = 100
GROUP BY e.environment

but I don't feel too confortable using the two max() functions like that...

Is there a better way? I have seen this question which joins on the date, but that scares me too because there could theoretically be another row with the same date.

Upvotes: 2

Views: 82

Answers (1)

The Phil Lee
The Phil Lee

Reputation: 633

I think something like this will work:

SELECT  v.id, 
        e.environment, 
        v.date
FROM (  SELECT MAX(v2.id) AS id
        FROM vHistory v2
          INNER JOIN instance i2
            ON v2.instance = i2.id
          INNER JOIN environment e2
            ON i2.environment = e2.idEnv
        WHERE v2.component = 100
        GROUP BY e2.environment
      ) AS maxid
        JOIN vHistory v
        JOIN instance i
        JOIN environment e
          ON (  maxid.id = v.id
            AND v.instance = i.id
            AND i.environemnt = e.idEnv
           )

maxid will only contain the latest ids of the rows that match your query. Joining those ids to the whole table should return only the info you need.

Upvotes: 1

Related Questions