Given a date find the previous &/or current and next x# Dates in MySQL non-linear

I have a table code_prices that looks something like this:

CODE   |   DATE     | PRICE
ABC    | 25-7-2011  |  2.81
ABC    | 23-7-2011  |  2.52
ABC    | 22-7-2011  |  2.53
ABC    | 21-7-2011  |  2.54
ABC    | 20-7-2011  |  2.58
ABC    | 17-7-2011  |  2.42
ABC    | 16-7-2011  |  2.38

The problem with the data set is there are gaps in the dates, so I may want to look for the price of item ABC on the 18th however there is no entry because the item wasnt sold on this date. So I would like to return the most recent hisotrical entry for the price.

Say if I query on the date 19-7-2011, I would like to return the entry on the 17th then the next 10 avalaible entries.

If however I query for the price of ABC on the 20th, I would want to return the price on the 20th and the next 10 prices after that...

What is the most efficient way to go about this either开发者_运维百科 in SQL statement or using a stored proc.

I can think of just writing a stored proc which takes the date as a param and then querying for all rows where DATE >= QUERY-DATE ordering by the date and then selecting the 11 items (via limit). Then basically I need to see if that set contains the current date, if it does then return, otherwise I will need to return the 10 most recent entires out of those 11 and also do another query on the table to return the previous entry by getting the max date where date < QUERY-DATE. I am thinking there might be a better way, however I'm not an expert with SQL (clearly)...

Thanks!

This is for one specific code:

SELECT code, `date`, price
FROM code_prices
WHERE code = @inputCode
  AND `date` >=
        ( SELECT MAX(`date`)
          FROM code_prices
          WHERE code = @inputCode
            AND `date` <= @inputDate
        )
ORDER BY `date`
LIMIT 11

For ABC and 19-7-2011, the above will you give the row for 17-7-2011 and the 10 subsequent rows (20-7-2011, 21-7-2011, etc)

I'm not entirely clear on what you want to achieve, but I'll have a go anyway. This searches for the ID of the row that contains a date less than or equal to your specified date. It then uses that ID to return all rows with an ID greater than or equal to that value. It assumes that you have a column other than the date column on which the rows can be ordered. This is because you said that the dates are non-linear - I assume that you must have some other way of ordering the rows.

  SELECT id, code, dt, price
    FROM code_prices
   WHERE id >= (
  SELECT id
    FROM code_prices
   WHERE dt <= '2011-07-24'
ORDER BY dt DESC
   LIMIT 1 )
ORDER BY id
   LIMIT 11;

Alternative with code condition - thanks to @ypercube for highlighting that ;-)

  SELECT id, code, dt, price
    FROM code_prices
   WHERE code = 'ABC'
     AND id >= (
  SELECT id
    FROM code_prices
   WHERE dt <= '2011-07-23'
     AND code = 'ABC'
ORDER BY dt DESC
   LIMIT 1 )
ORDER BY id
   LIMIT 11;