David, First of all, please post to the list in the future. I'm not always available to help with problems, and others may benefit from the problem/solution.
I would change your query to the following: 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; There are other ways to do it, but really none of them are all that pretty :) On Tue, 2001-10-30 at 12:27, David Wolf wrote: > Steve, > > I have one other (more complex) query I need some help on.. And you were > great help in the first query I did. I have a database with ~300,000 rows. I > want to find all properties that have been sold more than once within a x > month period (say, 12). I've tried: > > 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 year(a.solddate) = year(b.solddate) <--- This is obviously a bad > query line > ORDER BY a.addrdsp, a.solddate > LIMIT 200; > > > I can get it to work 'sort-of' It doesn't show properties where there are > more than 1 sale, and it doesn't (obviously) work where the two sold-dates > are with 12 months of each other. > > Can you help? Thanks in advance, > > 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
