List:General Discussion« Previous MessageNext Message »
From:shawn green Date:September 24 2013 3:05pm
Subject:Re: Problem with having
View as plain text  
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.

Yours,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
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