List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:September 23 2013 10:19pm
Subject:Re: Problem with having
View as plain text  
On Mon, Sep 23, 2013 at 2:17 PM, Sukhjinder K. Narula
<narula.sk@stripped>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 <larry.martell@stripped>wrote:
>
>> On Mon, Sep 23, 2013 at 1:51 PM, Sukhjinder K. Narula <
>> narula.sk@stripped> 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.martell@stripped>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
>>>>
>>>
>>>
>>
>

Thread
Problem with havingLarry Martell23 Sep
  • Re: Problem with havingSukhjinder K. Narula23 Sep
    • Re: Problem with havingLarry Martell23 Sep
      • Re: Problem with havingshawn green23 Sep
        • Re: Problem with havingAnanda Kumar23 Sep
        • Re: Problem with havingLarry Martell23 Sep
          • Re: Problem with havingshawn green24 Sep
            • Re: Problem with havingLarry Martell24 Sep
              • RE: Problem with havingRick James26 Sep
Re: Problem with havingLarry Martell23 Sep