GROUP BY problem

strui013

I have been struggling with this problem with almost 2 days now so mabey you guys can give me a helping hand.

My client has online games, one of them is a memory game. While playing the memory game the moves and time is beeing recored, the player with the least time and moves may call himself the winner of that day.

This client also wants an admin that contains reports so he can pay the winners. Now I need an overview with all the daily winners per hour, every hour there is a player which is the best player in that hour and I need that info to.

So obviously I need a group by per hour and a min(time + moves), the last thing works but the first doesn't. It doesn't group per hour I just get all records printed.

See here the SQL-statement:
SELECT ,time / 1000 AS time, DATE_FORMAT(timestamp, '%e.%c.%Y %H:%i:%S') AS timestamp, MIN(moves (time/1000)),hour(timestamp) AS HT
FROM game
WHERE DAYOFMONTH(timestamp) = DAYOFMONTH(NOW())
AND MONTH(timestamp) = MONTH(NOW())
AND YEAR(timestamp) = YEAR(NOW())
AND pid = 0
GROUP BY HT,id,pid,name,phone,time,moves
ORDER BY moves ASC

As you can see the group by contains multiple columns, I needed to do this otherwise it doesn't work at all. Though if I rip those columns off and only leave GROUP BY HT then I do get one result per hour but then the MIN() doesn't work correctly.

Can anyone help me with this ? :rolleyes:

drawmack

It's because you're misusing group by check the mysql manual for more information on group by:

it basically groups the entries where all of the fields grouped by matches.

Sxooter

Also, note that MySQL has a bug in its group by where it will let you select a column that is NOT grouped without using an aggregate function on it, which is illegal, according to the SQL spec. This leads to unpredictable results:

If I create a table in both MySQL and Postgresql like so:

create table test (a int, b int, c int);
insert into test values (1,1,1);
insert into test values (1,1,2);
insert into test values (1,1,3);
insert into test values (1,1,4);
insert into test values (1,2,1);
insert into test values (1,2,2);
insert into test values (1,2,3);
insert into test values (2,1,1);
insert into test values (2,1,2);
insert into test values (2,1,3);
insert into test values (2,1,4);

And then run the query:

select * from test group by a;

I get this from postgresql:

ERROR: attribute "test.b" must be GROUPed or used in an aggregate function

and rightly so. After all, which number from the second column do I want? Postgresql isn't gonna guess for me, and the SQL spec says to throw an error. In MySQL, I get this:

+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
+------+------+------+
2 rows in set (0.00 sec)

I.e. it just chose the first value it could find. The get the equivalent in a SQL spec database, you need this:

select a,max(b),max(c) from test group by a;
a | max | max
---+-----+-----
2 | 1 | 4
1 | 2 | 4

Or replace the max with min, etc...

My point being, and I have one, is that if you aren't grouping the field, then you're gonna just get whichever random row MySQL happens to grab, and your results may not be reproduceable. so, you need to add a max() or min() to your query if you aren't going to group by it.

Sxooter

FYI, starting MySQL with the --ansi switch will turn off the dubious "grab the first value" behaviour which can cause you to have a query that works, but at the same time, doesn't, but doesn't throw an error.