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]
