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
