[RESOLVED] Get two newest dates

[sidsel]

My query below works as it should except that I don't know how to fetch the two newest dates for use in the query. In my case CURDATE and CURDATE minus one day dosent work. Anyone with some input?

SELECT t1.currency, 

(SELECT t2.currency_rate FROM currency_rates AS t2 WHERE t2.id_date = '2007-12-05' AND t1.currency = t2.currency )  AS rate1,

(SELECT t2.currency_rate FROM currency_rates AS t2 WHERE t2.id_date = '2007-12-04' AND t1.currency = t2.currency )  AS rate2


 FROM currency_rates AS t1 GROUP BY t1.currency

[wilku]

You can use order by with limit in your subquery (or can't you?)

SELECT t1.currency, 

(SELECT t2.currency_rate FROM currency_rates AS t2 WHERE t1.currency = t2.currency ORDER BY t2.id_date DESC LIMIT 0,1)  AS rate1,

(SELECT t2.currency_rate FROM currency_rates AS t2 WHERE t1.currency = t2.currency ORDER BY t2.id_date DESC LIMIT 1,1)  AS rate2

 FROM currency_rates AS t1 GROUP BY t1.currency

I don't know if it's most efficient though.

[sidsel]

That seems to work