Disorder result with ORDER BY with ENUM, INT
-- 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 | -- .. -- -- -- 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 | -- .. -- 3. After running statement(2) I rerun statement(1) again and the result is then in order as (2). -- -- Dummy data & structure for testing is dumped as shown below. -- Thank you, -- -- -kk- -- 2003/11/26 -- -- MySQL dump 9.09 -- -- Host: localhostDatabase: test -- -- -- Server version 4.0.16 -- -- Table structure for table `report` -- CREATE TABLE report ( id int(10) unsigned NOT NULL default '0', type enum('general','inhouse') NOT NULL default 'general', PRIMARY KEY (id) ) TYPE=MyISAM COMMENT='Available Reports'; -- -- Dumping data for table `report` -- INSERT INTO report VALUES (1,'general'); INSERT INTO report VALUES (2,'general'); INSERT INTO report VALUES (3,'general'); INSERT INTO report VALUES (4,'general'); INSERT INTO report VALUES (5,'inhouse'); INSERT INTO report VALUES (6,'inhouse'); INSERT INTO report VALUES (7,'inhouse'); INSERT INTO report VALUES (8,'inhouse'); INSERT INTO report VALUES (9,'inhouse'); INSERT INTO report VALUES (10,'inhouse'); INSERT INTO report VALUES (11,'inhouse'); INSERT INTO report VALUES (12,'inhouse'); INSERT INTO report VALUES (13,'inhouse'); INSERT INTO report VALUES (14,'inhouse'); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Disorder result with ORDER BY with ENUM, INT
I'm using mySQL 4.0.14 Linux and the mySql CC 0.8.9 Beta. I ran your script and I'm clear. So its version specific. -Original Message- From: Kriengkrai J. [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 2:05 AM To: [EMAIL PROTECTED] Subject: Disorder result with ORDER BY with ENUM, INT -- 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 | -- .. -- -- -- 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 | -- .. -- 3. After running statement(2) I rerun statement(1) again and the result is then in order as (2). -- -- Dummy data & structure for testing is dumped as shown below. -- Thank you, -- -- -kk- -- 2003/11/26 -- -- MySQL dump 9.09 -- -- Host: localhostDatabase: test -- -- -- Server version 4.0.16 -- -- Table structure for table `report` -- CREATE TABLE report ( id int(10) unsigned NOT NULL default '0', type enum('general','inhouse') NOT NULL default 'general', PRIMARY KEY (id) ) TYPE=MyISAM COMMENT='Available Reports'; -- -- Dumping data for table `report` -- INSERT INTO report VALUES (1,'general'); INSERT INTO report VALUES (2,'general'); INSERT INTO report VALUES (3,'general'); INSERT INTO report VALUES (4,'general'); INSERT INTO report VALUES (5,'inhouse'); INSERT INTO report VALUES (6,'inhouse'); INSERT INTO report VALUES (7,'inhouse'); INSERT INTO report VALUES (8,'inhouse'); INSERT INTO report VALUES (9,'inhouse'); INSERT INTO report VALUES (10,'inhouse'); INSERT INTO report VALUES (11,'inhouse'); INSERT INTO report VALUES (12,'inhouse'); INSERT INTO report VALUES (13,'inhouse'); INSERT INTO report VALUES (14,'inhouse'); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disorder result with ORDER BY with ENUM, INT
Kriengkrai J. wrote: > -- 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]
Re: Disorder result with ORDER BY with ENUM, INT
Hi, - Original Message - From: "Chuck Gadd" Sent: Wednesday, November 26, 2003 2:29 PM Subject: Re: Disorder result with ORDER BY with ENUM, INT > Kriengkrai J. wrote: > > > -- 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. It doesn't look sorted to me ("general"s aren't sorted by id). :-) Shouldn't this be the order? ++-+ | id | type| ++-+ | 1 | general | | 2 | general | | 3 | general | | 4 | general | | 5 | inhouse | | 6 | inhouse | Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]