How to get price of correct date from a MySQL table -

i have following mysql table:

---+-------+------------+---------- id | price | e_date     | item_name ---+-------+------------+----------  1 | 1000  | 2015-01-01 | pen  2 | 1050  | 2015-02-01 | pen  3 | 800   | 2015-03-01 | pen  4 | 850   | 2015-03-20 | pen  5 | 1150  | 2015-04-01 | pen  6 | 500   | 2015-01-01 | pencil  7 | 550   | 2015-02-01 | pencil  8 | 700   | 2015-03-01 | pencil ---+-------+------------+---------- 

i want price of different days. suppose want price of 2015-02-15, price 1050. want price of 2015-03-15, price 800.

i used following query:

select max(price) table_name e_date<$date 

here $date comes user input (suppose user input 2015-03-15).

but max used, price returning 1050 on 2015-03-15 should 800.

how can right price single query , without using sub-query (if possible)?

try this:

select price table_name e_date <= $user_date order e_date desc limit 1; 

if have index on e_date , price, query not need hit table.

get highest price on or before supplied date

select price table_name e_date <= $user_date order e_date desc, price desc limit 1; 

the above query sorts records before current date date , price. if user_date 2015-02-15 , there multiple products, query sort data date , price , choose closest date first , highest price second. may or may not work efforts.

get highest price in current month

select price table_name e_date between date_format($user_date,'%y-%m-01') , $user_date order price desc limit 1; 

this query pulls records between beginning of month through user provided date. orders highest pricing first , shows highest price.

get highest price item in current month

select item_name, max(price) max_price table_name e_date between date_format($user_date,'%y-%m-01') , $user_date group item_name order max_price; 

example: http://sqlfiddle.com/#!9/8ce19/9

get price closest date (before or after)

select price (   (select *, datediff($user_date, e_date) days    table_name e_date <= $user_date   order e_date desc, price desc   limit 1)   union   (select *, datediff(e_date, $user_date) days    table_name e_date >= $user_date   order e_date, price desc   limit 1) ) closest order days, price desc limit 1; 

in query above, find 1 record before date , 1 record after given date. calculate days between dates , report price closest date. if there 2 closest days, additionally sort price.

alternate version:

select price test order abs(datediff(e_date, '2015-02-15')), price desc limit 1; 

example: http://sqlfiddle.com/#!9/8ce19/19 , http://sqlfiddle.com/#!9/8ce19/21