How do I retrieve 10 days data up to date 'XYZ' from mysql with Perl when some days data are missing, i.e. public holidays.

Question

Data is put into a MYSQL DB in the following format:

| 2010-03-18 | 1.580 | 1.590 | 1.560 | 1.580 | 164500 |     1.580 |
| 2010-03-19 | 1.570 | 1.570 | 1.560 | 1.570 | 178300 |     1.570 |
| 2010-03-22 | 1.550 | 1.560 | 1.540 | 1.560 | 309000 |     1.560 |
| 2010-03-23 | 1.560 | 1.560 | 1.550 | 1.550 | 284900 |     1.550 |

I need to select 10 days of data upto date XYZ, the problem is that some days are missing, i.e. public holidays.

Answer 1

If you want a ten-day range of data, use WHERE. As in,

SELECT * FROM table WHERE date >= '2010-03-22' AND date <= '2010-03-31' ;

If you want ten records, ending a certain day, use ORDER BY and LIMIT:

SELECT * FROM table WHERE date <= '2010-03-31' ORDER BY date DESC LIMIT 10 ;