Re: Problem with having
I Sent from my D - Reply message - From: "Rick James" To: "Larry Martell" , "shawn green" Cc: "mysql mailing list" 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 > wrote: > > > Hello Larry, > > > > > > On 9/23/2013 6:22 PM, Larry Martell wrote: > > > >> On Mon, Sep 23, 2013 at 3:15 PM, shawn green > >> ** > >> 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 > >>>> 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.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? > >>>>>> > >>
RE: Problem with having
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 > wrote: > > > Hello Larry, > > > > > > On 9/23/2013 6:22 PM, Larry Martell wrote: > > > >> On Mon, Sep 23, 2013 at 3:15 PM, shawn green > >> ** > >> 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 > >>>> 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.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 > >>
Re: Problem with having
On Tue, Sep 24, 2013 at 9:05 AM, shawn green wrote: > Hello Larry, > > > On 9/23/2013 6:22 PM, Larry Martell wrote: > >> On Mon, Sep 23, 2013 at 3:15 PM, shawn green ** >> 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 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.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
Hello Larry, On 9/23/2013 6:22 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 3:15 PM, shawn green 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 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 **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
On Mon, Sep 23, 2013 at 2:17 PM, Sukhjinder K. Narula wrote: > 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 wrote: > >> 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 >>> 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 >>> >>> >> >
Re: Problem with having
On Mon, Sep 23, 2013 at 3:15 PM, shawn green 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 >> 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 >>> **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
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 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 >> 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 >>> **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
Hi Larry, On 9/23/2013 3:58 PM, Larry Martell wrote: On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula 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 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
On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula 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 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 >> > >
Problem with having
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
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 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 >
Problem with HAVING
Hi,
I'm running Mysql 4.01 on mandrake 8.2 with all production data
using InnoDB type.
I've a field named "dch_pri" contain some account transaction data in
format of DECIMAL (14,2).
When running following query:
SELECT din_no, SUM(IF(tran_type = "D", dch_pri, -dch_pri)) AS dch_sum
FROM pa_ch2
WHERE x_status IN ("", "C")
AND dch_acc IN ('231000', '31')
GROUP BY din_no
HAVING dch_sum # 0
It remove all record that contain absolute value between 0 and 0.99
Change query to:
SELECT din_no, SUM(IF(tran_type = "D", dch_pri, -dch_pri)) AS dch_sum
FROM pa_ch2
WHERE x_status IN ("", "C")
AND dch_acc IN ('231000', '31')
GROUP BY din_no
HAVING dch_sum <> 0
It will retain all result record which dch_sum = 0
Change the HAVING dch_sum # 0 to HAVING dch_sum # 0.00
Or
Change the HAVING dch_sum <> 0 to HAVING dch_sum <> 0.00
Doesn't improve.
Any idea?
-
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
