mysql - SQL Query with multiple criteria -

how can write sql query select latest recommendation each analyst each stock known on specific date (e.g. 1/1/10). example, if analyst has multiple ratings, max(rating_date) greater specified date, should still return rating @ max date less specified date. so, in case below @ specified date of 1/1/10, should still rating john of 6/9/09 value smith of 12/5/09.

here's data structure:

stock_ticker,  analyst_name,  rating_date,  rating_name  g,             john,          6/9/09,       hold  g,             john,          1/20/10,      sell  g,             smith,         12/5/09,      buy 

if try sql below, value if max date less specified date.

select analyst_ratings.stock_ticker,         analyst_ratings.analyst_name,         analyst_ratings.rating_name  analyst_ratings rating_date in(     select max(rating_date)     analyst_ratings     rating_date <= '2010-01-01') 

select analyst_ratings.stock_ticker,     analyst_ratings.analyst_name,     analyst_ratings.rating_name  analyst_ratings rating_date in( select max(rating_date) analyst_ratings group stock_ticker, analyst_name rating_date <= '2010-01-01') 

try this