Hi Jocelyn, I don't have a quick/sure answer for you, but am wondering about the relationship between the date and topic columns - I note that the '429s are interspersed by other values.
I'm a great believer in the KISS principle (simple things amuse simple minds!) May I suggest taking out the last three clauses (ORDER BY, DESC, and LIMIT) and trying that. Then if it works, put the clauses back, one at a time (from left to right), observing the effect. Please let me know how you get on, =dn > So why does I obtain duplicates numbers for 'topic' with the first query > with DISTINCT ? (perhaps a bug ?) > > Regards, > > Jocelyn Fournier > > ----- Original Message ----- > From: "DL Neil" <[EMAIL PROTECTED]> > To: "Fournier Jocelyn [Presence-PC]" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Sunday, April 14, 2002 5:54 PM > Subject: Re: How does DISTINCT really work ? > > > > Hi Jocelyn, > > > > > I wonder how works DISTINCT when using it with the following join. > > > Ex : > > > > > > mysql> SELECT DISTINCT topic FROM > > > searchmainhardwarefr7,searchjoinhardwarefr7 WHERE > > > searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND > > > (mot='test') AND date >= '2002-03-24' ORDER BY date DESC LIMIT 0,20; > > > +--------+ > > > | topic | > > > +--------+ > > > | 108499 | > > > | 108341 | > > > | 108425 | > > > | 108425 | > > > | 108425 | > > > | 108429 | > > > | 108429 | > > > | 108429 | > > > | 108429 | > > > | 108427 | > > > | 108427 | > > > | 108429 | > > > | 108429 | > > > | 108429 | > > > | 108429 | > > > | 108429 | > > > | 108429 | > > > | 108429 | > > > | 108429 | > > > | 108429 | > > > +--------+ > > > 20 rows in set (0.08 sec) > > > > > > In this case I was expecting only distinct topic number list, like > > with the > > > following query : > > > > > > mysql> SELECT topic FROM searchmainhardwarefr7,searchjoinhardwarefr7 > > WHERE > > > searchmainhardwarefr7.numreponse=searchjoinhardwarefr7.numreponse AND > > > (mot='test') AND date >= '2002-03-24' GROUP BY topic ORDER BY date > > DESC > > > LIMIT 0,20; > > > +--------+ > > > | topic | > > > +--------+ > > > | 108499 | > > > | 108429 | > > > | 108427 | > > > | 108425 | > > > | 108417 | > > > | 108341 | > > > | 108312 | > > > | 108303 | > > > | 108275 | > > > | 108268 | > > > | 108167 | > > > | 108233 | > > > | 108231 | > > > | 108172 | > > > | 108052 | > > > | 108102 | > > > | 107835 | > > > | 108122 | > > > | 108096 | > > > | 106794 | > > > +--------+ > > > 20 rows in set (0.10 sec) > > > > > > Does DISTINCT implicitely check columns included in the WHERE clause ? > > > Thanks you. > > > > > > AFAIK the WHERE clause (join) is carried out earlier (etc) and the > > DISTINCT carried out on the resultset, almost at the end of the process. > > > > Regards, > > =dn > > > > > > --------------------------------------------------------------------- > > 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 > > > > > > --------------------------------------------------------------------- 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
