title: Top Group By DB2
body: <p>I've been trying for hours but can't get the query to do what I want using DB2.
  From table Company and Users I have the following tickets quantity info per company/user</p>
  
  <pre><code>user         company      quantity
  ------------ ------------ ------------
  mark         nissan       300
  tom          toyota       50
  steve        krysler      80
  mark         ford         20
  tom          toyota       120
  jose         toyota       230
  tom          nissan       145
  steve        toyota       10
  jose         krysler      35
  steve        ford         100
  </code></pre>
  
  <p>This is generated by the query:</p>
  
  <pre><code>SELECT T.USER, COUNT(T.USER) AS QUANTITY, T.COMPANY FROM TICKET T
  INNER JOIN COMPANY P ON P.COMPANY = T.COMPANY 
  GROUP BY (T.USER, T.COMPANY) -- ORDER BY QUANTITY DESC
  </code></pre>
  
  <p>What I want to see is the top user for each company, so given the data above, the query should show me:</p>
  
  <pre><code>user         company      quantity (Top user per company)
  ------------ ------------ --------------------------------
  mark         nissan       300
  jose         toyota       230
  steve        ford         100
  steve        krysler      80
  </code></pre>
  
  <p>How can I write the SQL to return this result?</p>
  
  <hr>
  
  <p>Final answer (noted in a comment):</p>
  
  <pre><code>SELECT user, quantity, company
    FROM (SELECT user, quantity, company,
                 RANK () OVER (PARTITION BY company ORDER BY quantity DESC) AS r
            FROM (SELECT T.USER, COUNT(T.USER) AS QUANTITY, T.COMPANY
                    FROM TICKET T JOIN COMPANY P ON P.COMPANY = T.COMPANY
                   GROUP BY (T.USER, T.COMPANY) ) s ) t
   WHERE r = 1;
  </code></pre>