On Mon, Sep 23, 2013 at 2:17 PM, Sukhjinder K. Narula <narula...@gmail.com>wrote:
> Hi, > > I see that. So the query seems to be picking the first entry out of the > after grouping by a field and displaying it. And it seems to make sense > since Having clause seems incomplete. I believe we need to complete the > condition by HAVING MIN(date_time) <, > or = something. > After reading this, I see what the problem is: http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html Then I read this: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html and changed it to this: 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 Which works, but is painfully slow. For a table with 200k rows it's been running for 25 minutes and isn't done yet. That will be unacceptable to my users. > On Mon, Sep 23, 2013 at 3:58 PM, Larry Martell <larry.mart...@gmail.com>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 >>>> >>> >>> >> >
