On Mon, Sep 23, 2013 at 3: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; > > 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