On Tue, Sep 24, 2013 at 9:05 AM, shawn green <shawn.l.gr...@oracle.com>wrote:
> Hello Larry, > > > On 9/23/2013 6:22 PM, Larry Martell wrote: > >> 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 >> >> > As I said, there are many many ways to solve this problem. Here is one > that is going to perform much better for the generic case than what you are > doing. > > CREATE TEMPORARY TABLE tmpMaxDates (key(recipe_id,maxdatetime)) > SELECT recipe_id, max(date_time) maxdatetime > FROM data_cstmeta > GROUP BY recipe_id; > > SELECT a.id, b.maxdatetime > FROM data_cstmeta a > INNER JOIN tmpMaxDates b > on a.recipe_id = b.recipe_id > and a.date_time = b.maxdatetime; > > DROP TEMPORARY TABLE tmpMaxDates; > > > Of course, an appropriate multi-column index on data_cstmeta would also > make your technique much faster than it is today. > > Thanks much Shawn! This ran in a few seconds vs. 30 minutes for my solution.
