Thomas
You might want to consider to post a sample select from the table, some
things become apparent just then.
Hannes
On 6/27/01 3:35 AM, "Thomas Michael Koch" <[EMAIL PROTECTED]> wrote:
> Hey Hans - 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
>
---------------------------------------------------------------------
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
