I got a table with a DATETIME column. When I manually get the latest datetime it says:
2009-09-17 08:45:29
2009-09-17 08:45:34
2009-09-17 08:45:55
2009-09-17 08:48:23
2009-09-17 08:51:06
BUT when i use MAX(date) it somehow gets these dates:
2009-09-17 08:45:30
2009-09-17 08:45:34
2009-09-17 08:45:55
2009-09-17 08:48:23
2009-09-17 08:51:07
(2009-09-17 08:45:29 is now 2009-09-17 08:45:30
2009-09-17 08:51:06 is now 2009-09-17 08:51:07)
Is there a way to fix this problem?
MAX(DATETIME) bug
Moderator: General Moderators
Re: MAX(DATETIME) bug
Post your full queries.
MAX will return a single result, so it's not a simple query (e.g. GROUP BY is involved)
MAX will return a single result, so it's not a simple query (e.g. GROUP BY is involved)
There are 10 types of people in this world, those who understand binary and those who don't
Re: MAX(DATETIME) bug
SELECT COUNT( DISTINCT pupil) FROM answer WHERE question='1' && answer='yes' && date IN ( SELECT MAX(date) FROM answer WHERE subject='English' && class=2 GROUP BY pupil);
Re: MAX(DATETIME) bug
An your query for " I manually get the latest datetime "
There are 10 types of people in this world, those who understand binary and those who don't
Re: MAX(DATETIME) bug
SELECT * FROM answer WHERE subject='English' && class=2 && question=1 && answer='yes'
This only gives me these 5 rows so it’s easy to see.
while
SELECT MAX(date) FROM answer WHERE subject='English' && class=2 GROUP BY pupil;
gives me the last 5 rows
This only gives me these 5 rows so it’s easy to see.
while
SELECT MAX(date) FROM answer WHERE subject='English' && class=2 GROUP BY pupil;
gives me the last 5 rows
Re: MAX(DATETIME) bug
These queries are too different.
What about:
[sql]SELECT MAX(`date`) FROM `answer` WHERE `subject`='English' AND `class`=2 AND `question`=1 AND `answer`='yes' GROUP BY `pupil`[/sql]
What about:
[sql]SELECT MAX(`date`) FROM `answer` WHERE `subject`='English' AND `class`=2 AND `question`=1 AND `answer`='yes' GROUP BY `pupil`[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
Re: MAX(DATETIME) bug
Thanks for the help!
I found the problem. I submitted many questions at the same time, but they didn't get the same timestamp.
When i moved "question='1'" it worked
SELECT COUNT( DISTINCT pupil) FROM answer WHERE && answer='yes' && date IN ( SELECT MAX(date) FROM answer WHERE subject='English' && class=2 && question='1' GROUP BY pupil);
I found the problem. I submitted many questions at the same time, but they didn't get the same timestamp.
When i moved "question='1'" it worked
SELECT COUNT( DISTINCT pupil) FROM answer WHERE && answer='yes' && date IN ( SELECT MAX(date) FROM answer WHERE subject='English' && class=2 && question='1' GROUP BY pupil);