This is something for my personal site, and nothing work related. On my weblog homepage, I want it to list all the posts in the last seven days that have posts. Here's what I'm doing right now:
SELECT DISTINCT E1.entry_id, E1.date_posted, E1.title, E1.body FROM entries AS E1, entries AS E2 WHERE E1.date_posted >= E2.date_posted GROUP BY E2.date_posted HAVING COUNT(DISTINCT TO_DAYS(E1.date_posted)) <= @nDays ORDER BY E1.date_posted DESC Where @nDays is the number of days to show. Now, this was a quick hack to get it working. It's far from efficient, and since I wrote it I've been searching for a better way of doing this but without any luck. Query caching makes it bearable, but it's those occasional 2 second lags when the cache times out that cause the pain. I'm using MySQL 4.0.6, so no subselects. Any ideas? -- Keith Gaughan, Developer Digital Crew Ltd., Pembroke House, Pembroke Street, Cork, Ireland http://www.digital-crew.com/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=36 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181819 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
