mysql query using MAX -

i hope can me. i've looked @ other threads not able find right answer.

i have mysql database following data:

id, company, datefrom, dateto, rate, point1, point2  1, dhl, 2014-03-01, 2014-03-31, $1000, paris, new york  2, mrw, 2014-03-01, 2014-03-31, $1300, paris, new york  3, exp, 2014-03-01, 2014-03-31, $1000, paris, new york  4, dhl, 2014-03-06, 2014-03-31, $900, paris, new york  5, fed, 2014-03-01, 2014-03-31, $1200, paris, new york 

i need query where, based on date, display rates valid on date onwards. example if today 2014-03-06, need result :

2, mrw, 2014-03-01, 2014-03-31, $1300, paris, new york  3, exp, 2014-03-01, 2014-03-31, $1000, paris, new york  4, dhl, 2014-03-06, 2014-03-31, $900, paris, new york  5, fed, 2014-03-01, 2014-03-31, $1200, paris, new york 

as can see, record 1 not displayed record 4 replaces record 1 of 2014-03-06

i have tried following query, worked:

select id, company, max(datefrom), dateto, rate, point1, point2 mydatabase datefrom<='2014-03-06' , datefrom>='2014-03-06' , point1='paris' , point2='new york' group company, point1, point2 

this query works great except selects companies dhl, selects fields record 1 datefrom record 4 !

what doing wrong?

make use of mysql's non-standard grouping support:

select * (   select * mydatabase   '2014-03-06' between datefrom , dateto   , point1 = 'paris'   , point2 = 'new york'   order datefrom desc) x group company 

this works first ordering matching rows latest-starting range first in inner query, grouping not non-aggregate columns, mysql selects first row each unique value of company, first row being 1 want (having been ordered).

note reworking of condition use between more elegant.