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 >> > >
