MySQL Forums :: Newbie :: Selecting max for each day


Advanced Search

Selecting max for each day
Posted by: scurvy rat ()
Date: November 26, 2009 10:12AM

I'm trying to get the max value from a column for each day in a db. i can get the max without a problem, but the date returned is just the first entry for that date, not the datetime associated with the max value.

the db has a field s1 decimal(5,2) and insert_time datetime

the select i'm trying is:
SELECT max(s1) AS max,insert_time,DATE_FORMAT(insert_time,'%Y-%m-%d') AS ymd from TABLE group by ymd

I get the max values no problem, but the insert_time is for a different row (specifically the first entry for that day.

+--------+---------------------+------------+
| max | insert_time | ymd |
+--------+---------------------+------------+
| 142.30 | 2009-06-21 00:00:30 | 2009-06-21 |
| 140.70 | 2009-06-22 00:00:30 | 2009-06-22 |
| 140.70 | 2009-06-23 00:00:30 | 2009-06-23 |
+--------+---------------------+------------+

on the 21st of june the insert_time that has s1=142.30 is 2009-06-21 09:16:30.

any help appreciated!

Options: ReplyQuote


Subject Written By Posted
Selecting max for each day scurvy rat 11/26/2009 10:12AM
Re: Selecting max for each day laptop alias 11/26/2009 10:22AM
Re: Selecting max for each day Rick James 11/29/2009 06:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.