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....
gameid int (index) non unique
weaponid int (index) non unique
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)
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
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)
group by gameid,weaponid, amount
from QueryB where rn = 1
I thank you for your time in advance. I