MySQL query to get two most recent records before a given DateTime

Question

I have an application that keeps track of prices for various products. Prices might change for any given product at any time during any day, and will not change regularly. There is a PriceHistory table that has dated records for all of the prices for the products over time. Users of the application can select a time and date and see what the prices were at that time. I use a query like this to get the "current" prices for all products at any given day and time:

SELECT A.*
FROM `PriceHistory` A
INNER JOIN (
    SELECT `BrandKey`, `LocationKey`, `ProductKey`, max(`EffectiveDateTime`) AS MaxUpdateDate
    FROM `PriceHistory`
    WHERE `EffectiveDateTime` <= '2010-02-22 12:00:00'
    GROUP BY `BrandKey`, `LocationKey`, `ProductKey`
) AS B ON 
A.`BrandKey` = B.`BrandKey`
AND A.`LocationKey` = B.`LocationKey`
AND A.`ProductKey` = B.`ProductKey`
AND A.`EffectiveDateTime` = B.MaxUpdateDate

Now I need to be able to show users how much the price changed when that price went into effect. So I need to get the two most recent prices for the selected time instead of just one. I'm pretty stumped. I've tried a couple of things and I'm just getting SQL errors because my attempted joins aren't legal. Any help would be appreciated.

I can loop over all the products and get the two most recent prices, but that's taking about 10 seconds for 350 products and 75,000 price rows. I don't necessarily need all products in one query, but running queries for each product won't be fast enough.

Answer 1

A query against the table, sorted by EffectiveDateTime in DESC order, with LIMIT 2 should do it.

SELECT A.*
FROM `PriceHistory` A
WHERE <clauses for selecting a product and brand go here>
ORDER BY `EffectiveDateTime` DESC
LIMIT 2

Answer 2

I would break it into 2 selects..

1st - select max(EffectiveDateTime) ... from price history where EffectiveDateTime <= '2010-02-22 12:00:00'

step 2 ) This requires that you have a numeric (autoinc type) PK in the priceHistory table. add it if it doesn't exist. Assume this column is called PriceHistoryID

part a) grab all the numeric PK's from the last query, and implode them into a var $ignoreRows

part b) get the max date ignoring the most recent set of data that you want to diff against .. i.e. get the second to last most recent data.

select max(EffectiveDateTime) ... from price history where EffectiveDateTime <= '2010-02-22 12:00:00' AND priceHistoryID NOT IN ($ignoreRows)

Note, if you only have 1 data point in the price history, it may not match up 1-1 , but that is a relatively minor thing you can fix in code, I would imagine...

Answer 3

If an auto_increment column is defined as the last component of a composite key, that column acts as a counter of all rows with the same values.

EG.

v1,v2,v3,1
v1,v2,v3,2
v1,v2,v4,1
...

You can use this in a temporary table built from your history table to rank all the entries with the same BrandKey, LocationKey, ProductKey on descending date value.

create temporary table ph ( 
    `BrandKey` varchar(10),
    `LocationKey` varchar(10), 
    `ProductKey` varchar(10), 
    `EffectiveDateTime`datetime,
    rank int not null auto_increment,
    primary key(BrandKey, LocationKey, ProductKey, rank);

insert into ph
   select `BrandKey`, `LocationKey`, `ProductKey`, `EffectiveDateTime`, NULL
   FROM `PriceHistory`
   WHERE `EffectiveDateTime` <= '2010-02-22 12:00:00'
   order by EffectiveDateTime desc;

delete from ph where rank > 2;

This temporary table will now contain 1 or 2 rows per BrandKey, LocationKey, ProductKey, which can then easliy be joined against your Price table - using a self (left) join if you need to include both the current and previous price in the same row.