mysql and dates

[ph1b3r]

I have a table like this:

user | date | value |
-------|------------|-----------|
mark | 13/04/2007 | 1
peter | 11/04/2007 | 2
peter | 10/04/2007 | 1
mark | 11/04/2007 | 3
peter | 01/04/2007 | 10
mark | 01/04/2007 | 1
mark | 04/03/2007 | 34
peter | 01/03/2007 | 3

i need make a select who give me value where the date its the more near to NOW() for any user.

thanks!

 

[r937]

select user
     , date
     , value
  from daTable as mysister
 where date =
       ( select max(date)
           from daTable
          where user = mysister.user
            and date <= current_date )

r937.com | rudy.ca

 

[guelphdad]

rudy's query won't work for you. it will when you convert your invalid dates that are in char/varchar format to yyyymmdd format with an actual DATE type. Use STR_TO_DATE to convert them.

 

[amir4lamp]

what version of mysql are you using???

 

[ph1b3r]

i use mysql 4.0.

i gonna try this one.

 

[r937]

you need to be on 4.1 to run a query with a subquery

r937.com | rudy.ca

 

[guelphdad]

This item will explain how to update your table by creating a new date type column and converting your dates. you can do this while being on mysql 4.0