Try
SELECT robotId, max(startTime) as crit
FROM RobotRun
WHERE startTime >= '2001-06-26 00:00:00'
AND endTime IS NOT NULL
GROUP BY robotId
This should return the robotId and the most recent startTime labeled 'crit'
for all records specified in the where clause. And have a look in the manual
for the group by statement - since this statement works like a enhanced
'select distinct' query it will produce ill results applied to non unique
combinations in the select and group by statement. I suppose thatıs the case
for your 'robotId, startTime' pair.
If I got this wrong forgive me it was first thing in the morning.
Hannes
On 6/26/01 4:05 AM, "Thomas Michael Koch" <[EMAIL PROTECTED]> wrote:
> 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
>
---------------------------------------------------------------------
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
