I have a table called location which has one to many relations with rates table. The following is a description of the rate table:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| location_id | bigint(20)   | YES  | MUL | NULL    |                |
| price       | double       | NO   |     | NULL    |                |
| start_date  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

How could I get the latest price which has most recent start_date? I tried many queries the following query is one of them, but I still can't get the correct result:

SELECT *
FROM rate r
     INNER JOIN (SELECT r2.start_date,r2.id AS id,
                        r2.location_id,
                        MIN(ABS(DATEDIFF(r2.start_date, NOW()))) AS version
                FROM rate r2
                GROUP BY r2.location_id) o2
     ON (o2.id = r.id AND r.start_date = o2.start_date);

What am I doing wrong? how can I fix it?

This question appears about once a week, just with different data. My latest answer is here.

Your query will look like this:

select  l.*, r.price
from    location l
join    rate     r
    on  r.Location_ID = l.ID
    and r.Start_Date =(
        select  Max( Start_Date )
        from    rate
        where   Location_ID = r.Location_ID );

You could allow future dates for Start_Date, for time when a rate change is scheduled ahead of time. In that case, the subquery would need a slight change to pull out the current rate which is the latest rate still in the past:

        ...
        where   Location_ID = r.Location_ID
            and Start_Date <= CurDate() );

Assuming the PK of the Rate table is (Location_ID, Start_Date), this index will provide very fast lookup of the exact row you're looking for.

Yes, I can see the PK for the Rate table is a surrogate key. Bad idea. Have you ever or do you think you will ever access the Rate table using the surrogate key value? Look at your own query. You have the Location_ID to access the Rate table -- and now the Start_Date value. Those two fields form a natural unique key and they are also the values you will have on hand when you access the table. There is your best primary key.