RE: Problem with having

2013-09-25 Thread Rick James 
Still more to this saga

Comment 1:
... HAVING x;
The expression ( x ) is evaluated as a true/false value, based on whether x is 
nonzero (true) or zero (false).  Your 'x' is  MIN(date_time) , which is very 
likely to be nonzero, hence TRUE.  That is, the HAVING does nothing useful.

Comment 2:
This shows
1. a technique
2. how MariaDB optimizes it away, and
3. how you can get MariaDB to still do the group by trick:
https://mariadb.com/kb/en/group-by-trick-has-been-optimized-away/
(I do not know of other MySQL variants that have any trouble with the trick.)

 -Original Message-
 From: Larry Martell [mailto:larry.mart...@gmail.com]
 Sent: Tuesday, September 24, 2013 1:44 PM
 To: shawn green
 Cc: mysql mailing list
 Subject: Re: Problem with having
 
 On Tue, Sep 24, 2013 at 9:05 AM, shawn green
 shawn.l.gr...@oracle.comwrote:
 
  Hello Larry,
 
 
  On 9/23/2013 6:22 PM, Larry Martell wrote:
 
  On Mon, Sep 23, 2013 at 3:15 PM, shawn green
  shawn.l.gr...@oracle.com**
  wrote:
 
   Hi Larry,
 
 
  On 9/23/2013 3:58 PM, Larry Martell wrote:
 
   On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
  narula...@gmail.comwrote:
 
Hi,
 
 
  In your second query, you seem to have MIN(date_time), but you are
  talking about maximum. So your group by query is actually pulling
  the minimum date for this recipe.
 
 
   I pasted the wrong query in. I get the same results regardless of
  if I
  have
  MIN or MAX - I get the id of the max, but the date_time of the min.
 
 
 
On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell 
  larry.mart...@gmail.com
 
  **wrote:
 
 
I want to find the rows from a table that have the max date_time
  for
 
  each
  recipe. I know I've done this before with group by and having,
  but I can't seem to get it to work now. I get the correct row id,
  but not the correct date_time. I'm sure I'm missing something
  simple.
 
  For purposes of showing an example, I'll use one recipe, 19166.
 
 
  For that recipe here's the row I would want:
 
  mysql select id, MAX(date_time) from data_cstmeta  where
  mysql recipe_id =
  19166;
  +-+-----+
  | id  | MAX(date_time)  |
  +-+-----+
 
  | 1151701 | 2013-02-07 18:38:13 |
  +-+-----+
 
  1 row in set (0.01 sec)
 
  I would think this query would give me that - it gives me the
  correct id, but not the correct date_time:
 
  mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta
  mysql where
  recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
  +-+-----+
  | id  | MaxDateTime |
  +-+-----+
 
  | 1151701 | 2010-12-13 16:16:55 |
  +-+-----+
 
  1 row in set (0.01 sec)
 
  How can I fix this?
 
  Thanks!
  -larry
 
 
   You have to do a two-stage match. One stage to find the MAX() of
  a
  value
  for each recipe_id, the other to match that MAX() to one or more
  rows to give you the best ID values.
 
  Here's a subquery method of doing it.  There are many many others
  (google for groupwize maximum)
 
  SELECT a.id, b.MaxDateTime
  FROM data_cstmeta a
  INNER JOIN (
   SELECT MAX(date_time) MaxDateTime
   FROM data_cstmeta
   WHERE recipe_id = 19166
  ) b
 on b.MaxDateTime = a.date_time
  WHERE recipe_id = 19166;
 
 
   Having the recipe_id in the query was just to show an example. I
  really
  want the id's with the max date for each recipe_id:
 
  This is what I changed it to, which works, but is too slow. I need to
  find a more efficient solution:
 
  SELECT d1.id, d1.date_time as MaxDateTime
  FROM data_cstmeta d1
  LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
  d1.date_time  d2.date_time
  WHERE d2.recipe_id IS NULL
 
 
  As I said, there are many many ways to solve this problem. Here is one
  that is going to perform much better for the generic case than what
  you are doing.
 
  CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime)) SELECT
  recipe_id, max(date_time) maxdatetime FROM data_cstmeta GROUP BY
  recipe_id;
 
  SELECT a.id, b.maxdatetime
  FROM data_cstmeta a
  INNER JOIN tmpMaxDates b
on a.recipe_id = b.recipe_id
and a.date_time = b.maxdatetime;
 
  DROP TEMPORARY TABLE tmpMaxDates;
 
 
  Of course, an appropriate multi-column index on data_cstmeta would
  also make your technique much faster than it is today.
 
 
 Thanks much Shawn! This ran in a few  seconds vs. 30 minutes for my
 solution.

Re: Problem with having

2013-09-25 Thread rob.poll...@gmail.com 
I

Sent from my D

- Reply message -
From: Rick James rja...@yahoo-inc.com
To: Larry Martell larry.mart...@gmail.com, shawn green 
shawn.l.gr...@oracle.com
Cc: mysql mailing list mysql@lists.mysql.com
Subject: Problem with having
Date: Thu, Sep 26, 2013 12:11 PM


Still more to this saga

Comment 1:
... HAVING x;
The expression ( x ) is evaluated as a true/false value, based on whether x is 
nonzero (true) or zero (false).  Your 'x' is  MIN(date_time) , which is very 
likely to be nonzero, hence TRUE.  That is, the HAVING does nothing useful.

Comment 2:
This shows
1. a technique
2. how MariaDB optimizes it away, and
3. how you can get MariaDB to still do the group by trick:
https://mariadb.com/kb/en/group-by-trick-has-been-optimized-away/
(I do not know of other MySQL variants that have any trouble with the trick.)

 -Original Message-
 From: Larry Martell [mailto:larry.mart...@gmail.com]
 Sent: Tuesday, September 24, 2013 1:44 PM
 To: shawn green
 Cc: mysql mailing list
 Subject: Re: Problem with having
 
 On Tue, Sep 24, 2013 at 9:05 AM, shawn green
 shawn.l.gr...@oracle.comwrote:
 
  Hello Larry,
 
 
  On 9/23/2013 6:22 PM, Larry Martell wrote:
 
  On Mon, Sep 23, 2013 at 3:15 PM, shawn green
  shawn.l.gr...@oracle.com**
  wrote:
 
   Hi Larry,
 
 
  On 9/23/2013 3:58 PM, Larry Martell wrote:
 
   On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
  narula...@gmail.comwrote:
 
Hi,
 
 
  In your second query, you seem to have MIN(date_time), but you are
  talking about maximum. So your group by query is actually pulling
  the minimum date for this recipe.
 
 
   I pasted the wrong query in. I get the same results regardless of
  if I
  have
  MIN or MAX - I get the id of the max, but the date_time of the min.
 
 
 
On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell 
  larry.mart...@gmail.com
 
  **wrote:
 
 
I want to find the rows from a table that have the max date_time
  for
 
  each
  recipe. I know I've done this before with group by and having,
  but I can't seem to get it to work now. I get the correct row id,
  but not the correct date_time. I'm sure I'm missing something
  simple.
 
  For purposes of showing an example, I'll use one recipe, 19166.
 
 
  For that recipe here's the row I would want:
 
  mysql select id, MAX(date_time) from data_cstmeta  where
  mysql recipe_id =
  19166;
  +-+-----+
  | id  | MAX(date_time)  |
  +-+-----+
 
  | 1151701 | 2013-02-07 18:38:13 |
  +-+-----+
 
  1 row in set (0.01 sec)
 
  I would think this query would give me that - it gives me the
  correct id, but not the correct date_time:
 
  mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta
  mysql where
  recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
  +-+-----+
  | id  | MaxDateTime |
  +-+-----+
 
  | 1151701 | 2010-12-13 16:16:55 |
  +-+-----+
 
  1 row in set (0.01 sec)
 
  How can I fix this?
 
  Thanks!
  -larry
 
 
   You have to do a two-stage match. One stage to find the MAX() of
  a
  value
  for each recipe_id, the other to match that MAX() to one or more
  rows to give you the best ID values.
 
  Here's a subquery method of doing it.  There are many many others
  (google for groupwize maximum)
 
  SELECT a.id, b.MaxDateTime
  FROM data_cstmeta a
  INNER JOIN (
   SELECT MAX(date_time) MaxDateTime
   FROM data_cstmeta
   WHERE recipe_id = 19166
  ) b
 on b.MaxDateTime = a.date_time
  WHERE recipe_id = 19166;
 
 
   Having the recipe_id in the query was just to show an example. I
  really
  want the id's with the max date for each recipe_id:
 
  This is what I changed it to, which works, but is too slow. I need to
  find a more efficient solution:
 
  SELECT d1.id, d1.date_time as MaxDateTime
  FROM data_cstmeta d1
  LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
  d1.date_time  d2.date_time
  WHERE d2.recipe_id IS NULL
 
 
  As I said, there are many many ways to solve this problem. Here is one
  that is going to perform much better for the generic case than what
  you are doing.
 
  CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime)) SELECT
  recipe_id, max(date_time) maxdatetime FROM data_cstmeta GROUP BY
  recipe_id;
 
  SELECT a.id, b.maxdatetime
  FROM data_cstmeta a
  INNER JOIN tmpMaxDates b
on a.recipe_id = b.recipe_id
and a.date_time = b.maxdatetime;
 
  DROP TEMPORARY TABLE tmpMaxDates;
 
 
  Of course, an appropriate multi-column index on data_cstmeta would
  also make your technique much faster than it is today.
 
 
 Thanks much Shawn! This ran in a few  seconds vs. 30 minutes for my
 solution.

Re: Problem with having

2013-09-24 Thread shawn green 

Hello Larry,

On 9/23/2013 6:22 PM, Larry Martell wrote:

On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.comwrote:


Hi Larry,


On 9/23/2013 3:58 PM, Larry Martell wrote:


On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
narula...@gmail.comwrote:

  Hi,


In your second query, you seem to have MIN(date_time), but you are
talking about maximum. So your group by query is actually pulling the
minimum date for this recipe.



I pasted the wrong query in. I get the same results regardless of if I
have
MIN or MAX - I get the id of the max, but the date_time of the min.



  On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com

**wrote:

  I want to find the rows from a table that have the max date_time for

each
recipe. I know I've done this before with group by and having, but I
can't
seem to get it to work now. I get the correct row id, but not the
correct
date_time. I'm sure I'm missing something simple.

For purposes of showing an example, I'll use one recipe, 19166.


For that recipe here's the row I would want:

mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
19166;
+-+---**--+
| id  | MAX(date_time)  |
+-+---**--+
| 1151701 | 2013-02-07 18:38:13 |
+-+---**--+
1 row in set (0.01 sec)

I would think this query would give me that - it gives me the correct
id,
but not the correct date_time:

mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
+-+---**--+
| id  | MaxDateTime |
+-+---**--+
| 1151701 | 2010-12-13 16:16:55 |
+-+---**--+
1 row in set (0.01 sec)

How can I fix this?

Thanks!
-larry



You have to do a two-stage match. One stage to find the MAX() of a value
for each recipe_id, the other to match that MAX() to one or more rows to
give you the best ID values.

Here's a subquery method of doing it.  There are many many others (google
for groupwize maximum)

SELECT a.id, b.MaxDateTime
FROM data_cstmeta a
INNER JOIN (
 SELECT MAX(date_time) MaxDateTime
 FROM data_cstmeta
 WHERE recipe_id = 19166
) b
   on b.MaxDateTime = a.date_time
WHERE recipe_id = 19166;



Having the recipe_id in the query was just to show an example. I really
want the id's with the max date for each recipe_id:

This is what I changed it to, which works, but is too slow. I need to find
a more efficient solution:

SELECT d1.id, d1.date_time as MaxDateTime
FROM data_cstmeta d1
LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
d1.date_time  d2.date_time
WHERE d2.recipe_id IS NULL



As I said, there are many many ways to solve this problem. Here is one 
that is going to perform much better for the generic case than what you 
are doing.


CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime))
SELECT recipe_id, max(date_time) maxdatetime
FROM data_cstmeta
GROUP BY recipe_id;

SELECT a.id, b.maxdatetime
FROM data_cstmeta a
INNER JOIN tmpMaxDates b
  on a.recipe_id = b.recipe_id
  and a.date_time = b.maxdatetime;

DROP TEMPORARY TABLE tmpMaxDates;


Of course, an appropriate multi-column index on data_cstmeta would also 
make your technique much faster than it is today.


Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Problem with having

2013-09-24 Thread Larry Martell 
On Tue, Sep 24, 2013 at 9:05 AM, shawn green shawn.l.gr...@oracle.comwrote:

 Hello Larry,


 On 9/23/2013 6:22 PM, Larry Martell wrote:

 On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.com**
 wrote:

  Hi Larry,


 On 9/23/2013 3:58 PM, Larry Martell wrote:

  On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
 narula...@gmail.comwrote:

   Hi,


 In your second query, you seem to have MIN(date_time), but you are
 talking about maximum. So your group by query is actually pulling the
 minimum date for this recipe.


  I pasted the wrong query in. I get the same results regardless of if I
 have
 MIN or MAX - I get the id of the max, but the date_time of the min.



   On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell 
 larry.mart...@gmail.com

 **wrote:


   I want to find the rows from a table that have the max date_time for

 each
 recipe. I know I've done this before with group by and having, but I
 can't
 seem to get it to work now. I get the correct row id, but not the
 correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+-----+
 | id  | MAX(date_time)  |
 +-+-----+

 | 1151701 | 2013-02-07 18:38:13 |
 +-+-----+

 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct
 id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+-----+
 | id  | MaxDateTime |
 +-+-----+

 | 1151701 | 2010-12-13 16:16:55 |
 +-+-----+

 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry


  You have to do a two-stage match. One stage to find the MAX() of a
 value
 for each recipe_id, the other to match that MAX() to one or more rows to
 give you the best ID values.

 Here's a subquery method of doing it.  There are many many others (google
 for groupwize maximum)

 SELECT a.id, b.MaxDateTime
 FROM data_cstmeta a
 INNER JOIN (
  SELECT MAX(date_time) MaxDateTime
  FROM data_cstmeta
  WHERE recipe_id = 19166
 ) b
on b.MaxDateTime = a.date_time
 WHERE recipe_id = 19166;


  Having the recipe_id in the query was just to show an example. I really
 want the id's with the max date for each recipe_id:

 This is what I changed it to, which works, but is too slow. I need to find
 a more efficient solution:

 SELECT d1.id, d1.date_time as MaxDateTime
 FROM data_cstmeta d1
 LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
 d1.date_time  d2.date_time
 WHERE d2.recipe_id IS NULL


 As I said, there are many many ways to solve this problem. Here is one
 that is going to perform much better for the generic case than what you are
 doing.

 CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime))
 SELECT recipe_id, max(date_time) maxdatetime
 FROM data_cstmeta
 GROUP BY recipe_id;

 SELECT a.id, b.maxdatetime
 FROM data_cstmeta a
 INNER JOIN tmpMaxDates b
   on a.recipe_id = b.recipe_id
   and a.date_time = b.maxdatetime;

 DROP TEMPORARY TABLE tmpMaxDates;


 Of course, an appropriate multi-column index on data_cstmeta would also
 make your technique much faster than it is today.


Thanks much Shawn! This ran in a few  seconds vs. 30 minutes for my
solution.

Re: Problem with having

2013-09-23 Thread Sukhjinder K. Narula 
Hi,

In your second query, you seem to have MIN(date_time), but you are talking
about maximum. So your group by query is actually pulling the minimum date
for this recipe.

Regards.

On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote:

 I want to find the rows from a table that have the max date_time for each
 recipe. I know I've done this before with group by and having, but I can't
 seem to get it to work now. I get the correct row id, but not the correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+-+
 | id  | MAX(date_time)  |
 +-+-+
 | 1151701 | 2013-02-07 18:38:13 |
 +-+-+
 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+-+
 | id  | MaxDateTime |
 +-+-+
 | 1151701 | 2010-12-13 16:16:55 |
 +-+-+
 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry

Re: Problem with having

2013-09-23 Thread Larry Martell 
On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
narula...@gmail.comwrote:

 Hi,

 In your second query, you seem to have MIN(date_time), but you are
 talking about maximum. So your group by query is actually pulling the
 minimum date for this recipe.


I pasted the wrong query in. I get the same results regardless of if I have
MIN or MAX - I get the id of the max, but the date_time of the min.



 On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote:

 I want to find the rows from a table that have the max date_time for each
 recipe. I know I've done this before with group by and having, but I can't
 seem to get it to work now. I get the correct row id, but not the correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+-+
 | id  | MAX(date_time)  |
 +-+-+
 | 1151701 | 2013-02-07 18:38:13 |
 +-+-+
 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+-+
 | id  | MaxDateTime |
 +-+-+
 | 1151701 | 2010-12-13 16:16:55 |
 +-+-+
 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry

Re: Problem with having

2013-09-23 Thread shawn green 

Hi Larry,

On 9/23/2013 3:58 PM, Larry Martell wrote:

On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
narula...@gmail.comwrote:


Hi,

In your second query, you seem to have MIN(date_time), but you are
talking about maximum. So your group by query is actually pulling the
minimum date for this recipe.



I pasted the wrong query in. I get the same results regardless of if I have
MIN or MAX - I get the id of the max, but the date_time of the min.




On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.comwrote:


I want to find the rows from a table that have the max date_time for each
recipe. I know I've done this before with group by and having, but I can't
seem to get it to work now. I get the correct row id, but not the correct
date_time. I'm sure I'm missing something simple.

For purposes of showing an example, I'll use one recipe, 19166.


For that recipe here's the row I would want:

mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
19166;
+-+-+
| id  | MAX(date_time)  |
+-+-+
| 1151701 | 2013-02-07 18:38:13 |
+-+-+
1 row in set (0.01 sec)

I would think this query would give me that - it gives me the correct id,
but not the correct date_time:

mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
+-+-+
| id  | MaxDateTime |
+-+-+
| 1151701 | 2010-12-13 16:16:55 |
+-+-+
1 row in set (0.01 sec)

How can I fix this?

Thanks!
-larry



You have to do a two-stage match. One stage to find the MAX() of a value 
for each recipe_id, the other to match that MAX() to one or more rows to 
give you the best ID values.


Here's a subquery method of doing it.  There are many many others 
(google for groupwize maximum)


SELECT a.id, b.MaxDateTime
FROM data_cstmeta a
INNER JOIN (
SELECT MAX(date_time) MaxDateTime
FROM data_cstmeta
WHERE recipe_id = 19166
) b
  on b.MaxDateTime = a.date_time
WHERE recipe_id = 19166;

Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Problem with having

2013-09-23 Thread Ananda Kumar 
select recipe_id,max(maxdatetime) from data_csmeta group by recipe_id
having recipe_id=19166;


On Mon, Sep 23, 2013 at 4:15 PM, shawn green shawn.l.gr...@oracle.comwrote:

 Hi Larry,


 On 9/23/2013 3:58 PM, Larry Martell wrote:

 On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
 narula...@gmail.comwrote:

  Hi,

 In your second query, you seem to have MIN(date_time), but you are
 talking about maximum. So your group by query is actually pulling the
 minimum date for this recipe.


 I pasted the wrong query in. I get the same results regardless of if I
 have
 MIN or MAX - I get the id of the max, but the date_time of the min.



  On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com
 **wrote:

  I want to find the rows from a table that have the max date_time for
 each
 recipe. I know I've done this before with group by and having, but I
 can't
 seem to get it to work now. I get the correct row id, but not the
 correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+---**--+
 | id  | MAX(date_time)  |
 +-+---**--+
 | 1151701 | 2013-02-07 18:38:13 |
 +-+---**--+
 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct
 id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+---**--+
 | id  | MaxDateTime |
 +-+---**--+
 | 1151701 | 2010-12-13 16:16:55 |
 +-+---**--+
 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry


 You have to do a two-stage match. One stage to find the MAX() of a value
 for each recipe_id, the other to match that MAX() to one or more rows to
 give you the best ID values.

 Here's a subquery method of doing it.  There are many many others (google
 for groupwize maximum)

 SELECT a.id, b.MaxDateTime
 FROM data_cstmeta a
 INNER JOIN (
 SELECT MAX(date_time) MaxDateTime
 FROM data_cstmeta
 WHERE recipe_id = 19166
 ) b
   on b.MaxDateTime = a.date_time
 WHERE recipe_id = 19166;

 Yours,
 --
 Shawn Green
 MySQL Principal Technical Support Engineer
 Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
 Office: Blountville, TN

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: Problem with having

2013-09-23 Thread Larry Martell 
On Mon, Sep 23, 2013 at 3:15 PM, shawn green shawn.l.gr...@oracle.comwrote:

 Hi Larry,


 On 9/23/2013 3:58 PM, Larry Martell wrote:

 On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula
 narula...@gmail.comwrote:

  Hi,

 In your second query, you seem to have MIN(date_time), but you are
 talking about maximum. So your group by query is actually pulling the
 minimum date for this recipe.


 I pasted the wrong query in. I get the same results regardless of if I
 have
 MIN or MAX - I get the id of the max, but the date_time of the min.



  On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell larry.mart...@gmail.com
 **wrote:

  I want to find the rows from a table that have the max date_time for
 each
 recipe. I know I've done this before with group by and having, but I
 can't
 seem to get it to work now. I get the correct row id, but not the
 correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+---**--+
 | id  | MAX(date_time)  |
 +-+---**--+
 | 1151701 | 2013-02-07 18:38:13 |
 +-+---**--+
 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct
 id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+---**--+
 | id  | MaxDateTime |
 +-+---**--+
 | 1151701 | 2010-12-13 16:16:55 |
 +-+---**--+
 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry


 You have to do a two-stage match. One stage to find the MAX() of a value
 for each recipe_id, the other to match that MAX() to one or more rows to
 give you the best ID values.

 Here's a subquery method of doing it.  There are many many others (google
 for groupwize maximum)

 SELECT a.id, b.MaxDateTime
 FROM data_cstmeta a
 INNER JOIN (
 SELECT MAX(date_time) MaxDateTime
 FROM data_cstmeta
 WHERE recipe_id = 19166
 ) b
   on b.MaxDateTime = a.date_time
 WHERE recipe_id = 19166;


Having the recipe_id in the query was just to show an example. I really
want the id's with the max date for each recipe_id:

This is what I changed it to, which works, but is too slow. I need to find
a more efficient solution:

SELECT d1.id, d1.date_time as MaxDateTime
   FROM data_cstmeta d1
   LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
d1.date_time  d2.date_time
   WHERE d2.recipe_id IS NULL

Re: Problem with having

2013-09-23 Thread Larry Martell 
On Mon, Sep 23, 2013 at 2:17 PM, Sukhjinder K. Narula
narula...@gmail.comwrote:

 Hi,

 I see that. So the query seems to be picking the first entry out of the
 after grouping by a field and displaying it. And it seems to make sense
 since Having clause seems incomplete. I believe we need to complete the
 condition by HAVING MIN(date_time) ,  or = something.


After reading this, I see what the problem is:

http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

Then I read this:

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

and changed it to this:

SELECT d1.id, d1.date_time as MaxDateTime
   FROM data_cstmeta d1
   LEFT JOIN data_cstmeta d2 ON d1.recipe_id = d2.recipe_id AND
d1.date_time  d2.date_time
   WHERE d2.recipe_id IS NULL

Which works, but is painfully slow. For a table with 200k rows it's been
running for 25 minutes and isn't done yet. That will be unacceptable to my
users.


 On Mon, Sep 23, 2013 at 3:58 PM, Larry Martell larry.mart...@gmail.comwrote:

 On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula 
 narula...@gmail.com wrote:

 Hi,

 In your second query, you seem to have MIN(date_time), but you are
 talking about maximum. So your group by query is actually pulling the
 minimum date for this recipe.


 I pasted the wrong query in. I get the same results regardless of if I
 have MIN or MAX - I get the id of the max, but the date_time of the min.



 On Mon, Sep 23, 2013 at 3:35 PM, Larry Martell 
 larry.mart...@gmail.comwrote:

 I want to find the rows from a table that have the max date_time for
 each
 recipe. I know I've done this before with group by and having, but I
 can't
 seem to get it to work now. I get the correct row id, but not the
 correct
 date_time. I'm sure I'm missing something simple.

 For purposes of showing an example, I'll use one recipe, 19166.


 For that recipe here's the row I would want:

 mysql select id, MAX(date_time) from data_cstmeta  where recipe_id =
 19166;
 +-+-+
 | id  | MAX(date_time)  |
 +-+-+
 | 1151701 | 2013-02-07 18:38:13 |
 +-+-+
 1 row in set (0.01 sec)

 I would think this query would give me that - it gives me the correct
 id,
 but not the correct date_time:

 mysql SELECT id, date_time as MaxDateTime  FROM data_cstmeta  where
 recipe_id = 19166 group by recipe_id HAVING MIN(date_time);
 +-+-+
 | id  | MaxDateTime |
 +-+-+
 | 1151701 | 2010-12-13 16:16:55 |
 +-+-+
 1 row in set (0.01 sec)

 How can I fix this?

 Thanks!
 -larry