[works but is TOO slow:]
SELECT store_num,net_sale,week_date
FROM weekly w1
WHERE net_sale=(
SELECT MAX(w2.net_sale)
FROM weekly w2
WHERE w1.store_num = w2.store_num)
AND w1.week_date < '20070722'
ORDER BY w1.store_num
[works much faster but doesn't limit week_date]
SELECT wk1.store_num, wk1.week_date, wk1.net_sale
FROM weekly wk1
LEFT JOIN weekly wk2 on wk1.net_sale<wk2.net_sale and wk1.store_num=wk2.store_num
WHERE wk2.net_sale is null
ORDER BY wk1.store_num
I've tried adding (to the where clause of the joined table)...
and wk1.net_sale<'20070722'
and wk1.net_sale<'20070722'
and (wk1.week_date<'20070722' and wk2.week_date<'20070722')
...none of which give sane or useful results. Ideas? Recommendation for different approach?