MySQL Forums
Forum List  »  Newbie

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

Written By
Selecting max for each day
November 26, 2009 10:12AM
November 26, 2009 10:22AM
November 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.