Mysql join based on max(timestamp)

Question

I have a web app that lets a client define a daily rate for their service. For any given day, the client can enter two rates, an hourly rate (rateTypeId=1) and a dailyRate (rateTypeId=2). These rates are often assigned in advance, and they often change. I need to track all assignments, but only pull the latest assigned rate.

I have two tables. The first table simply defines my rate structure and looks like this (simplified):

Table: RateDefinitions

RATECODE ----- RATE
31 ---------------- 5.00
32 ---------------- 6.00
33 ---------------- 7.00

My second table tracks the rates assigned to given dates. More than one rate can be assigned to a given date, but we will only used the latest rate based on the 'entrytimestamp'.

Table: Rates

ID --- RATETYPEID --- RATECODE ------ DATE -------- ENTRYTIMESTAMP
1 ---------- 1 --------------- 31 ---------- 20091010 ---------- 1100000000
2 ---------- 2 --------------- 33 ---------- 20091010 ---------- 1100000000
3 ---------- 1 --------------- 32 ---------- 20091010 ---------- 1200000000

Now I'm having difficulty putting together a query that will pull all the latest rate assignments for a given timeframe.

I've tried:

select r.id, r.rateTypeId, r.rateCode, max(r.entryTimestamp), rd.rate
from rates r
join rateDefinitions rd
on r.rateCode=rd.rateCode
where date=20091010
group by startDate, rateTypeId

But that's not going to do it. I'm thinking I need a join on a subselect statement, but not sure. My results should contain two rows per date, similar to:

ID --- RATETYPEID --- RATECODE ---------- ENTRYTIMESTAMP ----- RATE
3 ----------- 1 --------------- 32 -------------------- 1200000000 ----------6.00
2 ----------- 2 --------------- 33 -------------------- 1100000000 ----------7.00

Thanks for any suggestions.

Answer 1

I recommend a sub-query:

select r.id, r.rateTypeId, r.rateCode, max(r.entryTimestamp), rd.rate 
from  
rateDefinitions rd,
rate r,
    (select 
        rateCode, max(entryTimestamp) maxEntryTimestamp
    from
        rate
    where date=20091010
    group by rateCode) latestRates

where r.rateCode=rd.rateCode  AND
    r.rateCode = latestRates.rateCode AND
    r.entryTimestamp = latestRates.maxEntryTimestamp
group by startDate, rateTypeId

There's a big assumption here on my part. That is that in the rate table, for a given date and ratecode, the entrytimestamps are unique.

Answer 2

This problem is fairly common. You need a subquery to throw out the max timestamp and the ratetypeid (which is the basis for grouping) and then select everything else from an inner join of this subquery rows and everything else.

For MySQL:

SELECT ratecode, rate, id, ratetypeid, date, entrytimestamp 

FROM ratedefinitions, 
(SELECT ratetypeid, MAX(entrytimestamp) AS max_timestamp FROM Rates 
GROUP BY ratetypeid) AS inner_table

WHERE

inner_table.ratetypeid = ratetypeid
AND innertable.max_timestamp = timestamp