stcuppet, Wednesday, July 31, 2002, 3:04:45 PM, you wrote: >>Description:
s> When joining tables using a SELECT statement and using the DISTINCTROW modifier, I noticed a difference in the resultset by adding the ORDER BY directive on the end of the SQL command on a column s> that is not included in the result. If no join is performed and the DISTINCTROW/ORDER BY combination is used, the results remain correct; however, by joining the tables by a common ID, all rows s> with a different value in the column being ORDERed BY are returned. I have also repeated this bug on MySQL 3.23.49a on RedHat 7.2 using the official build on InnoDB tables. >>How-To-Repeat: [skip] s> # ------------------------------ s> # Reading out correct statements s> # ------------------------------ s> SELECT DISTINCTROW email, shipcode FROM table1, table2 WHERE table1.infoID=table2.infoID; s> SELECT DISTINCTROW email FROM table1 ORDER BY dateentered DESC; s> # ----------------------------- s> # Reading out erratic statement s> # ----------------------------- s> SELECT DISTINCTROW email, shipcode FROM table1, table2 WHERE table1.infoID=table2.infoID s> ORDER BY dateentered DESC; In your "correct" statement you haven't used dateentered column. If you re-write query as SELECT DISTINCTROW email, shipcode, dateentered FROM table1, table2 WHERE table1.infoID=table2.infoID; You det the following result: +----------------------+----------+---------------------+ | email | shipcode | dateentered | +----------------------+----------+---------------------+ | [EMAIL PROTECTED] | Z001 | 2002-07-30 22:56:38 | | [EMAIL PROTECTED] | Z001 | 2002-07-27 22:58:16 | | [EMAIL PROTECTED] | Z001 | 2002-06-19 15:22:19 | | [EMAIL PROTECTED] | Z001 | 2002-05-19 22:17:32 | | [EMAIL PROTECTED] | R002 | 2002-06-18 14:23:47 | +----------------------+----------+---------------------+ 5 rows in set (0.01 sec) In your "erratic" statement you get the same data ordered by dateentered column. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ 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
