hean, Friday, August 02, 2002, 7:44:31 AM, you wrote: h> Description: h> When we join two or more tables, select distinct and order by on a h> column, we get problems. h> How-To-Repeat:
[skip] h> This is the problem: h> mysql> select distinct a1.a from test as a1, test as a2 order by a2.b; h> +------+ h> | a | h> +------+ h> | 1 | h> | 2 | h> | 1 | h> | 2 | h> | 1 | h> | 2 | h> +------+ h> 6 rows in set (0.00 sec) h> mysql> select distinct a1.a from test as a1, test as a2 order by a1.b; h> +------+ h> | a | h> +------+ h> | 1 | h> | 2 | h> | 1 | h> +------+ h> 3 rows in set (0.01 sec) h> Interestingly, the query without the "order by" works... :) h> mysql> select distinct a1.a from test as a1, test as a2; h> +------+ h> | a | h> +------+ h> | 1 | h> | 2 | h> +------+ h> 2 rows in set (0.00 sec) h> Fix: h> Err... avoid putting order by or distinct together? If you need both h> ... err... bad luck. Columns that are specified in the ORDER BY clause are included in the column list, but they are 'hidden', not displayed in the output. So, if you re-write queries like: mysql> SELECT DISTINCT a1.a, a1.b FROM test as a1, test as a2 ORDER BY a1.b; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | | 1 | 3 | +------+------+ and mysql> SELECT DISTINCT a1.a, a2.b FROM test as a1, test as a2 ORDER BY a2.b; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 1 | | 1 | 2 | | 2 | 2 | | 1 | 3 | | 2 | 3 | +------+------+ You can see that results are correct. -- 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
