Selecting max() of multiple columns

Question

Ok, here's my table:

product_id  version_id  update_id  patch_id
  1           1           0          0
  1           1           1          0
  1           1           1          1
  1           1           2          0
  1           1           2          1
  2           1           0          0
  2           2           0          0
  2           3           0          0
  2           3           0          1
  3           1           0          0
  3           1           0          1

Now I want to select the latest version of a product, so the version with the highest update_id & patch_id.

For example, the latest version of

• product 1 should return 1, 2, 1
• product 2 should return 3, 0, 1
• product 3 should return 1, 0, 1

I was trying all kinds of stuff with GROUP BY and HAVING, tried subqueries, but I still can't figure out a way to accomplish this.

Can anybody help me out to find the right query, or should I think of writing a php function for this?

Edit

Some additional info: - The columns together are the primary key (there are more colums, but for this problem they don't matter) - None of the columns is auto-increment

This is the table:

CREATE  TABLE IF NOT EXISTS `db`.`patch` (
`product_id` INT NOT NULL ,
`version_id` INT NOT NULL ,
`update_id` INT NOT NULL ,
`patch_id` INT NOT NULL
PRIMARY KEY (`product_id`, `version_id`, `update_id`, `patch_id`) ,
INDEX `fk_patch_update1` (`product_id` ASC, `version_id` ASC, `update_id` ASC) )

Edit 2

Flagged as duplicate, it is not: The other question looks for records higher than a value for any of the three different columns.

In this question we look for the highest version number grouped by the product_id.

Edit 3

rgz's answer tells me again that this is a duplicate. First of all: this question is older. Secondly, I don't think the answer is the same.

rgz suggests using the following query:

SELECT product_id, GREATEST(version_id, update_id, patch_id) AS latest_version FROM patch.

GREATEST(1,2,3) returns 3, right? Wat if we have these values:

  product_id  version_id  update_id  patch_id
  1           1           0          0
  1           1           2          8
  1           3           0          0

As I understand, this query wil return:

  product_id  latest_version
  1                 1
  1                 8
  1                 3

But it should return:

  product_id  version_id update_id  patch_id
  1           3          0          0

I don't think GREATEST could help. If you think it will, please prove me wrong.

Answer 1

This is one example of when unique identifers come in useful.

Imagine you have an autoincrememnting ID field, you can then find the id you want for each product by using a correlated sub-query...

SELECT
  *
FROM
  yourTable
WHERE
  id = (
        SELECT   id
        FROM     yourTable AS lookup
        WHERE    lookup.product_id = yourTable.product_id
        ORDER BY version_id DESC, update_id DESC, patch_id DESC
        LIMIT    1
       )

The equivalent without a unique identifer requires multiple correlated sub-queries...

SELECT
  *
FROM
  yourTable
WHERE
     version_id = (
                   SELECT   MAX(version_id)
                   FROM     yourTable AS lookup
                   WHERE    lookup.product_id = yourTable.product_id
                  )
  AND update_id = (
                   SELECT   MAX(update_id)
                   FROM     yourTable AS lookup
                   WHERE    lookup.product_id = yourTable.product_id
                     AND    lookup.version_id = yourTable.version_id
                  )
  AND patch_id  = (
                   SELECT   MAX(patch_id)
                   FROM     yourTable AS lookup
                   WHERE    lookup.product_id = yourTable.product_id
                     AND    lookup.version_id = yourTable.version_id
                     AND    lookup.update_id  = yourTable.update_id
                  )

This would be significantly slower than on a table with a unique identifier column.

Another alternative (without a unique identifier) is to self-join on different levels of aggregation.

SELECT
  yourTable.*
FROM
  (SELECT product_id, MAX(version_id) AS max_version_id FROM yourTable GROUP BY product_id) AS version
INNER JOIN
  (SELECT product_id, version_id, MAX(update_id) AS max_update_id FROM yourTable GROUP BY product_id, version_id) AS update
    ON  update.product_id = version.product_id
    AND update.version_id = version.max_version_id
INNER JOIN
  (SELECT product_id, version_id, updatE_id, MAX(patch_id) AS max_patch_id FROM yourTable GROUP BY product_id, version_id) AS patch
    ON  patch.product_id = update.product_id
    AND patch.version_id = update.version_id
    AND patch.update_id  = update.max_update_id
INNER JOIN
  yourTable
    ON  yourTable.product_id = patch.product_id
    AND yourTable.version_id = patch.version_id
    AND yourTable.update_id  = patch.update_id
    AND yourTable.patch_id   = patch.max_patch_id