Hey Hannes - it didn't work (damn) but thank you for your reply and sorry
about the accidental mail you got.
The result that was produced had the correct time but it was not attatched
to the correct row. It was instead attached to the first row that MySql
found in the RobotRun table.
I have found another way around it, which goes like this:
SELECT max(runId), robotId
FROM RobotRun
WHERE endTime IS NOT NULL
GROUP BY RobotId
This builds on the assumption that runId's are assigned in a strictly
ascending order, so it only works for my domain ...
It also seems that I have made a small typo in my initial request (sorry).
The purpose of the SQL is to find the runId of the newest run for each
robotId. Thus I reformed your SQL to: SELECT runId, max(startTime)
I can solve my problem by using GROUP BY and HAVING when running against
SyBase like this:
SELECT *
FROM RobotRun
WHERE endTime IS NOT NULL
GROUP BY robotId
HAVING startTime = max(startTime)
Alas, this does not work with MySql.
Is there a general understanding within the MySql community that GROUP BY
and HAVING doesn't conform to the standard ?
Regards
Thomas
-----Original Message-----
From: Hannes Niedner [mailto:[EMAIL PROTECTED]]
Sent: 26. juni 2001 17:42
To: [EMAIL PROTECTED]
Subject: Re: HELP NEEDED: Problems with SELECT in combination with
HAVING
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
---------------------------------------------------------------------
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
