Max-based mysql connection (timestamp)

Question

I have a web application that allows the customer to determine the daily rate for their service. On any day, the client can enter two bets, an hourly rate (rateTypeId = 1) and dailyRate (rateTypeId = 2). These bets are often set in advance, and they often change. I need to track all appointments, but only pull out the last assigned speed.

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

Table: RateDefinitions

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

My second table tracks bids assigned to given dates. More than one bid may be assigned for a given date, but we will only use the most recent bid based on "entrytimestamp".

Table: Betting

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

Now it’s hard for me to put together a query that pulls out all the latest speed assignments for a given period of time.

I 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 it will not be done. I think I need to join the subselect team, but not sure. My results should contain two lines per day, 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

This problem is quite common. You need a subquery to throw out the maximum timestamp and ratetypeid (which is the basis for the grouping), and then select everything else from the inner join of these subquery strings 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 

Answer 2

I recommend the subquery:

 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 is a lot of speculation here on my part. That is, in the speed table, for a given date and speed code, unique entries are unique.