> -- System: MySQL 4.0.13, 4.0.16 on Linux x86 > -- Table type: MyISAM, InnoDB > -- Description / How-To-Repeat: > -- 1. When I use > -- SELECT id, type FROM test.report ORDER BY type, id; > -- the result is in wrong order > -- -- > -- +----+---------+ > -- | id | type | > -- +----+---------+ > -- | 4 | general | > -- | 3 | general | > -- | 1 | general | > -- | 2 | general | > -- | 5 | inhouse | > -- | 6 | inhouse | > -- ..
It is sorted EXACTLY as you specified. First by type, and then by ID.
If you want it sorted first by ID, then do ORDER BY id, type;
But, if your ID field is unique, then adding ",type" doesn't do ANYTHING.
> -- 2. But when I use > -- SELECT id, type FROM test.report ORDER BY type AND id; > -- the result is in right order > -- -- > -- +----+---------+ > -- | id | type | > -- +----+---------+ > -- | 1 | general | > -- | 2 | general | > -- | 3 | general | > -- | 4 | general | > -- | 5 | inhouse | > -- | 6 | inhouse | > -- ..
The AND between the two fields is doing a BINARY AND on the two values. It is meaningless in this case.
1 AND "general" = 0 2 AND "general" = 0 5 AND "inhouse" = 0
So the system is just giving them to you in the "natural" order, as if you didn't have an order by clause.
> -- 3. After running statement(2) I rerun statement(1) again and the result is then in order as (2).
you probably reversed the field order when you re-ran statement 1.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]