Top Group By DB2

Question

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

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

This is generated by the query:

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

What I want to see is the top user for each company, so given the data above, the query should show me:

user         company      quantity (Top user per company)
------------ ------------ --------------------------------
mark         nissan       300
jose         toyota       230
steve        ford         100
steve        krysler      80

How can I write the SQL to return this result?

---

Final answer (noted in a comment):

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;

Answer 1

Build it up step by step.

Find the maximum quantity for each company, assuming the first data table shown in the question is called 'Tickets':

SELECT Company, MAX(Quantity) AS MaxQuantity
  FROM Tickets
 GROUP BY Company;

Now, find the data for the user(s) with that maximum quantity for that company:

SELECT T.User, T.Company, M.MaxQuantity
  FROM Tickets AS T
  JOIN (SELECT Company, MAX(Quantity) AS MaxQuantity
          FROM Tickets
         GROUP BY Company) AS M
    ON T.Company = M.Company AND T.Quantity = M.MaxQuantity;

If the top quantity for a particular company was, say, 200 and two users both scored 200 for that company, then this query lists both users.

Now, if you mean that the query you show in the question generates the first result table, then what I called tickets just above needs to be the derived table:

SELECT T.User, COUNT(T.User) AS Quantity, T.Ccompany
  FROM Ticket AS T
 INNER JOIN Company AS P ON P.Company = T.Company 
 GROUP BY (T.User, T.Company)
 ORDER BY QUANTITY DESC 

In which case, we can use a WITH clause (syntax unchecked, but I think it is correct per SQL standard):

WITH Tickets AS
    (SELECT T.User, COUNT(T.User) AS Quantity, T.Ccompany
       FROM Ticket AS T
       JOIN Company AS P ON P.Company = T.Company 
      GROUP BY (T.User, T.Company)
    )
SELECT T.User, T.Company, M.MaxQuantity
  FROM Tickets AS T
  JOIN (SELECT Company, MAX(Quantity) AS MaxQuantity
          FROM Tickets
         GROUP BY Company) AS M
    ON T.Company = M.Company AND T.Quantity = M.MaxQuantity;

Clearly, you can also write the WITH sub-query out twice if you prefer.

Answer 2

This should work. Create a derived view to calculate the Quantity per user and per company. Then get the max of then Quantity and then join the max back to the the calculation of the quantity.

SELECT p.company, 
       t.user, 
       t.quantity 
FROM   (SELECT MAX(t.quantity) max_quantity, 
               t.company 
        FROM   (SELECT  
                       COUNT(t.user) quantity, 
                       t.company 
                FROM   ticket t 
                GROUP  BY t.company) t) maxq 
       INNER JOIN (SELECT t.user, 
                          t.company, 
                          COUNT(t.user) quantity 
                   FROM   ticket t 
                   GROUP  BY t.company, 
                             t.user) t 
         ON maxq.max_quantity = t.quantity 
            AND maxq.company = t.company 
       INNER JOIN company p 
         ON p.company = t.company 
ORDER  BY t.quantity DESC 

A working sample that shows the top users by tag for the StackOverflow data can be found here.