RE: HELP NEEDED: Problems with SELECT in combination with HAVING

2001-06-27 Thread Thomas Michael Koch 

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 5. 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

RE: HELP NEEDED: Problems with SELECT in combination with HAVING 2

2001-06-27 Thread Thomas Michael Koch 

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 5. 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

Re: HELP NEEDED: Problems with SELECT in combination with HAVING

2001-06-27 Thread Hannes Niedner 

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 5. 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:

HELP NEEDED: Problems with SELECT in combination with HAVING

2001-06-26 Thread Thomas Michael Koch 

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 5. 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

Re: HELP NEEDED: Problems with SELECT in combination with HAVING

2001-06-26 Thread Hannes Niedner 

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 5. 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