Hi, So I assume in this case the only way to have distinct results is to use "GROUP BY topic" clause ?
Regards, Jocelyn ----- Original Message ----- From: "Sinisa Milivojevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, April 26, 2002 4:45 PM Subject: Re: How does DISTINCT really work ? > Fournier Jocelyn [Presence-PC] writes: > > Hi, > > > > I have the latest 4.0.2 source installed, and the problem is still here > > AFAIK : > > > > mysql> \s > > -------------- > > mysql Ver 12.1 Distrib 4.0.2-alpha, for pc-linux-gnu (i686) > > > > -=[root@anedroide]=(/usr/local/mysql-4.0)# bk export -tpatch > > <(13:29:52) > > # This is a BitKeeper generated patch for the following project: > > # Project Name: MySQL - fast and reliable SQL database > > # This patch format is intended for GNU patch command version 2.5 or higher. > > # This patch includes the following deltas: > > # ChangeSet 1.1235 -> 1.1236 > > # Docs/manual.texi 1.865 -> 1.866 > > # > > # The following is the BitKeeper ChangeSet Log > > # -------------------------------------------- > > # 02/04/15 [EMAIL PROTECTED] 1.1236 > > # Documentation fix. > > # -------------------------------------------- > > > > mysql> SELECT DISTINCT topic FROM > > forum.searchmainpresencepc4,forum.searchjoinpresencepc4 WHERE > > searchmainpresencepc4.numreponse=searchjoinpresencepc4.numreponse AND > > (mot='les') ORDER BY date DESC LIMIT 0,40; > > +-------+ > > | topic | > > +-------+ > > | 11462 | > > <cut> > > | 12313 | > > | 9673 | > > | 11462 | > > <cut> > > | 12282 | > > | 9673 | > > | 12224 | > > +-------+ > > 40 rows in set (0.06 sec) > > > > Hi! > > Thank you for posting about the above problem and for uploading the tables. > > The above is not a bug. > > Due to the presence of date column in ORDER BY clause, MySQL (and any > other RDBMS) has to do a silent addition to the select list, so your > query truly looks like this (I had to change table names to check the > ones you have sent us): > > > SELECT DISTINCT topic, date FROM searchmainhardwarefr8,searchjoinhardwarefr8 WHERE > searchmainhardwarefr8.numreponse=searchjoinhardwarefr8.numreponse AND > (mot='les') ORDER BY date DESC LIMIT 0,40; > > And a true output is truly like this : > > topic date > 2171 2002-04-19 20:13:10 > 2126 2002-04-19 04:54:38 > 2146 2002-04-18 21:39:12 > 2147 2002-04-18 12:44:30 > 2148 2002-04-18 09:41:56 > 2148 2002-04-17 15:13:44 > 2148 2002-04-16 15:59:50 > 1873 2002-04-15 10:02:20 > 2132 2002-04-13 18:18:44 > 2133 2002-04-13 15:56:57 > 2129 2002-04-12 17:43:29 > 2121 2002-04-12 17:00:03 > 2085 2002-04-12 15:07:49 > 2121 2002-04-12 13:26:14 > 1664 2002-04-12 00:45:30 > 2106 2002-04-10 00:33:03 > 741 2002-04-09 14:50:31 > 2093 2002-04-09 09:12:46 > 2045 2002-04-08 11:59:09 > 2089 2002-04-07 18:29:08 > 2089 2002-04-07 16:53:36 > 2067 2002-04-07 15:05:28 > 2079 2002-04-05 16:57:08 > 2021 2002-04-05 01:51:33 > 2054 2002-04-03 11:03:42 > 2049 2002-03-29 11:42:59 > 1993 2002-03-28 01:28:08 > 2040 2002-03-27 16:50:19 > 2021 2002-03-24 12:29:28 > 2005 2002-03-21 16:55:01 > 2005 2002-03-20 17:58:37 > 1935 2002-03-19 21:06:12 > 1935 2002-03-19 18:31:35 > 1998 2002-03-19 17:51:20 > 1990 2002-03-18 21:22:52 > 1934 2002-03-18 10:50:45 > 1983 2002-03-18 00:06:59 > 1934 2002-03-17 22:57:50 > 1968 2002-03-15 16:41:51 > 1971 2002-03-14 14:00:35 > > > So, simply, everything is as it should be. > > -- > Regards, > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus > <___/ www.mysql.com > > --------------------------------------------------------------------- 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
