mysql min 最小时间,Mysql最小值和最大值和相应的“日期”每月

I have a table named "rates" and it has two fields "date" and "rate". I like to get MIN and MAX rate values and their dates on which they occurred for each month. But I could not manage.

SELECT date,

MIN(rate) AS minRate,

MAX(rate) AS maxRate,

MONTH(date) AS monthName,

YEAR(date) AS yearName

FROM rates

GROUP BY yearName ASC, monthName ASC

Clarification: I like to get something like this:

Months MIN mindate MAX maxdate

Jan 1.234 2012-01-13 1.534 2012-01-24

Feb 1.165 2012-02-28 1.373 2012-02-11

and so on

解决方案

try this query database name test is thier u can use yours or remove it

SELECT

MIN(rate) AS minRate,

(select date from test.rates where rate = min(co.rate) and month(date) = month(co.date) and year(date) = year(co.date) limit 1 )as min_date,

MAX(rate) AS maxRate,

(select date from test.rates where rate = max(co.rate) and month(date) = month(co.date) and year(date) = year(co.date) limit 1)as max_date

FROM test.rates co

GROUP BY year(date) , month(date)