1

Possible Duplicate:
Top Group By DB2

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

QUERY USING:

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 


user         company      quantity
------------ ------------ ------------
mark         nissn        300
tom          toyt         50
steve        kryr         80
mark         frd          20
tom          toyt         120
jose         toyt         230
tom          nissn        145
steve        toyt         10
jose         kryr         35
steve        frd          100

This should be the result (Top user per company)

user         company      quantity 
------------ ------------ --------------------------------
mark         nissn        300
jose         toyt         230
steve        frd          100
steve        kryr         80

As you can see there are many users in a company and each have different quantities per company. The result should get the user with the highest quantity per company. i.e. : Company nissn it has 2 users and each has (mark with 300) and (tom with 145) so it should give me the highest user which would be mark with 300. The same would be for toyt, frd, kryr. I need all of them in a query.

I wonder if that's possible in a query or I will need to create a stored procedure?

Community
  • 1
  • 1
Juan Carlos Costilla
  • 119
  • 1
  • 7
  • 15
  • On SO, patience is a virtue when asking a question. If your question does not get answered immediately, exercise your virtue. Or edit your question to make it clearer what you are asking for. Do not submit a second substantively identical question - especially not without cross-referencing the first and making it clear why the two are distinctly different despite any superficial similarities. (In this case, I don't think there are any significant differences; the company names are spelled differently, but that's about all.) – Jonathan Leffler Jun 13 '11 at 22:35
  • hello, i think it is better and more detailed explained i also changed the company data to be treated like code and not name because i thought that was the problem i couldn't get a right answear. the idea is the same but explained in a different way. – Juan Carlos Costilla Jun 13 '11 at 22:39
  • Well, opinions may differ, but I think it would be better to spend the effort on the original question, editing it to explain what is in the commentary. Your original question would have been clearer if the query was at the top as the generator of the first set of results - as here. In the first answer given to your other question, there's a transcription bug for the innermost instance of the 'generator query', leading to the error you reported. – Jonathan Leffler Jun 13 '11 at 22:44
  • 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 That's the answear for anyone watching this :) – Juan Carlos Costilla Jun 13 '11 at 22:52

0 Answers0