title: Selecting max() of multiple columns
body: <p>Ok, here's my table:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Now I want to select the latest version of a product, so the version with the highest update_id &amp; patch_id.</p>
  
  <p>For example, the latest version of</p>
  
  <ul>
  <li>product 1 should return 1, 2, 1</li>
  <li>product 2 should return 3, 0, 1</li>
  <li>product 3 should return 1, 0, 1</li>
  </ul>
  
  <p>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. </p>
  
  <p>Can anybody help me out to find the right query, or should I think of writing a php function for this?</p>
  
  <p><strong>Edit</strong> </p>
  
  <p>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</p>
  
  <p>This is the table:</p>
  
  <pre><code>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) )
  </code></pre>
  
  <p><strong>Edit 2</strong></p>
  
  <p>Flagged as duplicate, it is not: The other question looks for records higher than a value for any of the three different columns. </p>
  
  <p>In this question we look for the highest version number grouped by the product_id.</p>
  
  <p><strong>Edit 3</strong> </p>
  
  <p>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. </p>
  
  <p>rgz suggests using the following query: </p>
  
  <pre><code>SELECT product_id, GREATEST(version_id, update_id, patch_id) AS latest_version FROM patch.
  </code></pre>
  
  <p>GREATEST(1,2,3) returns 3, right? Wat if we have these values:</p>
  
  <pre><code>  product_id  version_id  update_id  patch_id
    1           1           0          0
    1           1           2          8
    1           3           0          0
  </code></pre>
  
  <p>As I understand, this query wil return:</p>
  
  <pre><code>  product_id  latest_version
    1                 1
    1                 8
    1                 3
  </code></pre>
  
  <p>But it should return:</p>
  
  <pre><code>  product_id  version_id update_id  patch_id
    1           3          0          0
  </code></pre>
  
  <p>I don't think GREATEST could help. If you think it will, please prove me wrong.</p>