troubleshooting Question

Simple group by causing me a problem

Avatar of mre224
mre224 asked on
Microsoft SQL ServerOracle DatabaseSQL
12 Comments1 Solution254 ViewsLast Modified:
I have a transational based table of games played  by gameid, by weaponid, with the amount of kills.  I want a report by gameid, of the weaponid with the highest amount.  Simple right?  This would be the same as saying give me the invoice type with the highest amount of the day per day.

Here is my schema... simplified....
autoid  identity
gameid int (index) non unique
weaponid int (index) non unique
amount int

gameid        weaponid amount
257095714        3          2
257095714        4          5
257095714        2          1
257095714        1          1
257095714        5          1

my inteded result should be the row..
257095714        4          5

Here is what i have tried, usuccessfully..
SELECT gameid ,Max(amount), max(weaponid)
FROM weapontrans
group by gameid
this one returns    257095714        5          5   which row doesnt even exists.   obviously max on weaponid is the problem.  In microsoft access, the funtion First() which says just give me the top row in that group by, is not available

I have also tried
SELECT gameid ,Max(amount), weaponid
FROM weapontrans
group by gameid, weaponid

but this will give me me every record deduped on weaponid

It seems this is obviously a schema issue, but i cannot change this and cannot belive such and easy query cannot be done without a schema change or multiple levels of sql statments like the working 2005 TSQL below that does work, but not the solution i am looking for.  

WITH QueryB AS (
SELECT gameid ,max(amount) as amount,weaponid,
rn = row_number() OVER (PARTITION BY gameid ORDER BY amount desc)
FROM weapontrans
group by gameid,weaponid, amount

select gameid,weaponid,amount
from QueryB  where rn = 1

I thank you for your time in advance.  I
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros