Select Max Date per Month 1

[SBpsc]

Using MySQL 4.1.5. I am pretty new to writing SQL queries so I am hoping someone can help me with this.

I have the following data in a database.

ID | Date | Value
------------------------
2 | June 3 | 10
2 | June 15 | 5
2 | June 20 | 7
2 | July 7 | 10
3 | June 8 | 2

What I need is the value for the max date for each month for each ID. So, my result should look like the following:

ID | Date | Value
------------------------
2 | June 20 | 7
2 | July 7 | 10
3 | June 8 | 2

Can anyone give me any ideas on how I can go about doing this? Thanks.

 

[r937]

what is the datatype of the `Date` column?

r937.com | rudy.ca

 

[SBpsc]

The datatype for the Date column is DateTime.

 

[r937]

try this --

select t.ID
     , t.Date
     , t.Value
  from yourtable as t
inner
  join (
       select ID
            , date_format(Date,'%Y%m') 
            , max(Date) as maxDate
         from yourtable
       group
           by ID
            , date_format(Date,'%Y%m')     
       ) as maxes
    on maxes.ID = t.ID
   and maxes.maxDate = t.Date

r937.com | rudy.ca

 

[SBpsc]

Thanks a bunch. It worked.


- SB