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

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