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!

[smantscheff]

Since you are mixing prices, pageViews and dates in mysql and european format it is not quite clear what you are aiming at.
Maybe you could try something along the lines:

select * from prices where date in
select max(date) as date1 from prices group by year(date),week(date)

Test:

drop table if exists prices;
create table prices (date date, price integer);
insert into prices values ('2000-01-01','1');
insert into prices values ('2000-01-02','2');
insert into prices values ('2000-01-03','3');
insert into prices values ('2000-01-04','4');
insert into prices values ('2000-01-05','5');
insert into prices values ('2000-01-06','6');
insert into prices values ('2000-01-07','7');
insert into prices values ('2000-01-08','8');
insert into prices values ('2000-01-09','9');
insert into prices values ('2000-01-11','11');
insert into prices values ('2000-01-12','22');
insert into prices values ('2000-01-13','33');
insert into prices values ('2000-01-14','44');
insert into prices values ('2000-01-15','55');
insert into prices values ('2000-01-16','66');
insert into prices values ('2000-01-17','77');
insert into prices values ('2000-01-18','88');
insert into prices values ('2000-01-19','99');

select * from prices where date in 
(select max(date) as date1 from prices group by year(date),week(date))
;


+------------+-------+
| date       | price |
+------------+-------+
| 2000-01-01 |     1 |
| 2000-01-08 |     8 |
| 2000-01-15 |    55 |
| 2000-01-19 |    99 |
+------------+-------+