Reputation: 83

MySQL date query syntax

If I have a date column, example "2013-05-05", how do I select the previous record? I tried

SELECT DATE, percent_change
FROM aa
WHERE DATE >2012 -12 -31
ORDER BY DATE DESC 
LIMIT 1

Upvotes: 2

Answers (2)

Reputation: 606

Assuming that date is a unique key:

SELECT `date`, percentage_change FROM aa WHERE `date`>'2012-12-31' ORDER BY `date` DESC LIMIT 1;

Then, to get the previous record:

SELECT `date`, percentage_change FROM aa WHERE `date`>'2012-12-31' ORDER BY `date` DESC LIMIT 1 OFFSET 1;

And the record before that:

SELECT `date`, percentage_change FROM aa WHERE `date`>'2012-12-31' ORDER BY `date` DESC LIMIT 1 OFFSET 2;

etc.

Upvotes: 1

Reputation: 62851

To get the record before a given date, this should work:

SELECT DATE, percent_change 
FROM aa 
WHERE DATE < '2013-05-05' 
ORDER BY DATE DESC
LIMIT 1

Upvotes: 0