asked on

Simple group by causing me a problem

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

Naveen Kumar

there are many ways of getting your requirement done. you can do it using subquery or analytical functions etc...

if you are looking for a solution in ORACLE sql, then try the below :

below is the one which uses a subquery:

select gameid, weaponid, amount
from weapontrans
where ( gameid, amount )
in ( SELECT gameid ,Max(amount)
FROM weapontrans
group by gameid );

which uses analytical functions...

select gameid ,weaponid, amount
from (
SELECT gameid ,weaponid, amount,
row_number() over( partition by gameid order by amount desc ) x
FROM weapontrans
group by gameid )
where x = 1;

irudyk

Try the following query

SELECT gameid, weaponid, Max(weapontrans.amount) AS amount
FROM weapontrans
GROUP BY gameid, weaponid
HAVING ((([gameid] & Max([amount])) In (SELECT [gameid] & Max(amount) AS Expr1 FROM weapontrans GROUP BY gameid;)));

Open in new window

mre224

ASKER

ok, besides the answer with row_count() , which i said i already know works. Why does this answer require a subquery or and IN statment.

Naveen Kumar

to whom are you asking the question ?

mre224

ASKER

you, everyone ,etc

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

gatorvip

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

gatorvip

>>I don't think you mean TSQL here[...]

Oops, I didn't realize this was cross-posted in SQL Server and Oracle. Ignore this bit.

calpurnia

I was chatting to my better half about this question, and he tells me it is possible to get the desired result without using a subquery in PostgreSQL, using the non-standard SELECT DISTINCT ON syntax.

SELECT DISTINCT ON (W.gameid) W.gameid, W.weaponid, W.amount
FROM weapontrans as W
ORDER BY W.gameid, W.amount DESC;

Of course, if two or more weapons tie for top place in a particular game, this will only give you the first one in the list, whereas using the subselect method will give you them all.

Naveen Kumar

do you need solution in oracle or PostgreSQL ? because i know only oracle.

peter991

I did a small lab on a Oracle DB.

SQL> select * from result;
 
    GAMEID   WEAPONID     AMOUNT
---------- ---------- ----------
 257095714          1          1
 257095714          2          1
 257095714          3          2
 257095714          4          5
 257095714          5          1
 
 
select gameid, weaponid, amount 
from result
where amount >=
(select max(amount) from result);
 
    GAMEID   WEAPONID     AMOUNT
---------- ---------- ----------
 257095714          4          5
 
 
SQL> insert into result values(257095714,6,100);
 
SQL> select * from result;
 
    GAMEID   WEAPONID     AMOUNT
---------- ---------- ----------
 257095714          1          1
 257095714          2          1
 257095714          3          2
 257095714          4          5
 257095714          5          1
 
 
SQL> insert into result values(257095714,6,100);
 
select gameid, weaponid, amount 
from result
where amount >=
(select max(amount) from result);
 
    GAMEID   WEAPONID     AMOUNT
---------- ---------- ----------
 257095714          6        100

Open in new window

gatorvip

>>select gameid, weaponid, amount
from result
where amount >=
(select max(amount) from result);
<<

This is not going to work for different gameid values, as you are just returning the row(s) that contain(s) the maximum amount value in the table. nav_kum_v provided the correct version that accounts for gameid.