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

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