mre224

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

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

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;)));
```

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.

to whom are you asking the question ?

ASKER

you, everyone ,etc

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**
>>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.

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

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.

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.

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

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
```

>>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.

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.

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;