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.gr...@oracle.com>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...@gmail.com>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.mart...@gmail.com> >>> **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 > >
