I'm experiencing a little weirdness that I was hoping some one could clear
up for me.
Take the following query of tab-separated category parents:
SELECT parents, name FROM Categories ORDER BY parents, name;
In a sample statement, this results in something like:
+----------------+--------+
| parents | name |
+----------------+--------+
| | cat 1 |
| | cat 2 |
| cat 1 | cat 1a |
| cat 2 | cat 2a |
+----------------+--------+
Which is what you'd assume. But as it gets more complex (multiple parents),
it starts acting weird:
+----------------+--------+
| parents | name |
+----------------+--------+
| | cat 1 |
| | cat 2 |
| cat 1 cat 1a | cat 1b |
| cat 2 cat 2a | cat 2b |
| cat 1 | cat 1a |
| cat 2 | cat 2a |
+----------------+--------+
I've solved this by using the following query:
SELECT RTRIM(CONCAT(parents, ' ')) AS parents, name FROM Categories ORDER BY
parents, name;
Note:
SELECT RTRIM(parents) AS parents, name FROM Categories ORDER BY parents,
name;
works SOMETIMES, but not in every case.
Which results in:
+----------------+--------+
| parents | name |
+----------------+--------+
| | cat 1 |
| | cat 2 |
| cat 1 | cat 1a |
| cat 2 | cat 2a |
| cat 1 cat 1a | cat 1b |
| cat 2 cat 2a | cat 2b |
+----------------+--------+
But this seems like a cop-out to me, since TECHNICALLY, the sort values of
these two statements should be identical, right?
Empty parents are nullstrings ('', not NULL), and there is no leading or
trailing space that would account for the behavior when using RTRIM.
Any ideas what's going on?
Thanks,
Chris
---------------------------------------------------------------------
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
