MySQL date query syntax

Question

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

do you want to select the previous record after ordering the record by date? — usha
– usha, Commented Jun 9, 2013 at 4:05

prothid · Accepted Answer · 2013-06-10 12:37:15Z

1

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.

answered Jun 9, 2013 at 3:54

prothid

6064 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

This returns the most recent entry, 2013-06-05.

Oh, I see what you're asking. I believe you are looking for the OFFSET syntax. See edits above.

sgeddes · Accepted Answer · 2013-06-09 03:58:32Z

0

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

answered Jun 9, 2013 at 3:58

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges