List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:September 24 2013 8:43pm
Subject:Re: Problem with having
View as plain text  
On Tue, Sep 24, 2013 at 9:05 AM, shawn green <shawn.l.green@stripped>wrote:

> 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.green@stripped>**
>> 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.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
>>>>>>
>>>>>>
>>>>>>  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.

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