Is it possible to do this with 1 query?

[ernest1a]

I save prices data for different days. If a day doesn't have any changes, it is not in the database.

table prices

date            | price

----------------------

2010-10-01 | 200

2010-10-02 | 200

2010-10-03 | 220

2010-10-06 | 260

2010-10-09 | 190

...

 

Now I need to prices for statistic purposes for 1 month, but only for every 7 days of the month. If there is not data in each 7 day, it checks what was the price for the most recent date that exists.

 

For this I make for each 7 day a query, so I need 4 queries

1. SELECT pageViews FROM stats WHERE date<=07.10.2010  ORDER BY date DESC

2. SELECT pageViews FROM stats WHERE date<=14.10.2010  ORDER BY date DESC

3. SELECT pageViews FROM stats WHERE date<=21.10.2010  ORDER BY date DESC

4. SELECT pageViews FROM stats WHERE date<=28.10.2010  ORDER BY date DESC

 

But is it possible to make this with 1 query?

 

tnx!

[fenway]

well, you can translate the date into a daynumber(), and then modulus appropriately for group by.