Hello there - I have this weird problem using MySql (version 3.23.39).
It seems that MySql interpretation of a SELECT statment using HAVING is
completely random.
I have a table called RobotRun which stores information about when a robot
has been running (start, stop and the id of the robot). This translates to
columns: "runId" (primary key), "robotId" (the id of the robot), "startTime"
and "endTime".
A robot can run several times during its life time, thus several records
will appear in RobotRun for the same robotId, however, each row will always
have a unique runId.
The purpose of the SQL is to produce one RobotRun row for each robot, with
the added restriction that it is only the row with the newest "startTime"
value that gets selected.
Now if I do the following SQL things start to get strange:
SELECT robotId, startTime, max(startTime) as crit
FROM RobotRun
WHERE startTime >= '2001-06-26 00:00:00'
AND endTime IS NOT NULL
GROUP BY robotId
HAVING startTime = crit
This query produces 321 rows.
Setting the startTime to '2001-06-25 00:00:00' produces 707 rows, which is
correct and expected since the input set is larger.
NOW: Setting the startTime to '2001-06-24 00:00:00' produces 21 rows!!!!
What is going on. I cannot see how this is possible. The input set is only
getting larger.
The number of rows involved in the table is in the vicinity of 50000. The
theoretical maximum number of rows produced from the SQL is in the
neighbourhood of 5000 rows. That shouldn't be a problem.
I have also tried the following statement with the same result (problem):
SELECT robotId, startTime
FROM RobotRun
WHERE startTime >= '2001-06-26 00:00:00'
AND endTime IS NOT NULL
GROUP BY robotId
HAVING startTime = max(startTime)
Any help would be appreciated.
Regards
Thomas Koch
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
