I'm using the following query SELECT DISTINCT a.addrdsp,a.listdate,a.solddate,a.lpricea,a.sprice FROM archive a, archive b WHERE a.status='s' AND a.addrdsp IS NOT NULL AND a.addrdsp = b.addrdsp AND a.solddate < b.solddate AND date_add(a.solddate, interval 1 year) > b.solddate ORDER BY a.addrdsp, a.solddate LIMIT 200;
I'm trying to look at about 300,000 rows of property data. I'm interested in knowing which properties have been sold 2 or more times within a 1 year period. However, when I run the query, I get 100 Brazeau Cresc SW and 100 Bridlewood Road SW in my list of properties--even though they have not had 2 or more 'sales' within a period of one year. I need to limit those properties which only occur once in the table. Also, even though all of the fields are indexed, the query takes up to 12 minutes to complete! Thanks David --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
