Announcement

Announcement Module
Collapse
No announcement yet.

Change of order of rows in table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Change of order of rows in table

    Hi,

    I am using MySQL 5.1.15-beta

    I have a table viz PLANTF.

    Field | Type | Null | Key | Default | Extra |
    +--------------------+-----------------------+------+-----+- ------------------+-------+
    | PLANID | smallint(5) unsigned | NO | PRI | 0 | |
    | PLANNAME | varchar(10) | NO | | | |
    | DESCRIPTION | mediumtext | YES | | NULL | |
    | INCREMENTAL | tinyint(3) unsigned | NO | | 0 | |
    | SUBSEQUENT | tinyint(3) unsigned | NO | | 0 | |
    | NRC | decimal(7,2) unsigned | NO | | 0.00 | |
    | PORTNRC | decimal(7,2) unsigned | NO | | 0.00 | |
    | MRC | decimal(7,2) unsigned | NO | | 0.00 | |
    | PORTMRC | decimal(7,2) unsigned | NO | | 0.00 | |
    | CONTRACTPERIOD | smallint(5) unsigned | NO | | 0 | |
    | EARLYCANCELCHARGES | decimal(7,2) unsigned | NO | | 0.00 | |
    | CREATED | timestamp | NO | | CURRENT_TIMESTAMP | |
    | CREATEDBY | varchar(25) | NO | | | |
    | MODIFIED | datetime | NO | | | |
    | MODIFIEDBY | varchar(25) | NO | | | |
    +--------------------+-----------------------+------+-----+- ------------------+-------+
    Now when the table was having ENGINE as INNODB the table looked as follows:

    SELECT * FROM PLANTF;

    +--------+----------+--------------------+-------------+---- --------+------+---------+-------+---------+---------------- +--------------------+---------------------+-----------+---- -----------------+------------+
    | PLANID | PLANNAME | DESCRIPTION | INCREMENTAL | SUBSEQUENT | NRC | PORTNRC | MRC | PORTMRC | CONTRACTPERIOD | EARLYCANCELCHARGES | CREATED | CREATEDBY | MODIFIED | MODIFIEDBY |
    +--------+----------+--------------------+-------------+---- --------+------+---------+-------+---------+---------------- +--------------------+---------------------+-----------+---- -----------------+------------+
    | 0 | default | default plan | 6 | 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
    | 1 | tulip | 2 cents per minute | 30 | 6 | 0.00 | 0.00 | 10.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
    | 2 | alfa | 5 cents per minute | 30 | 6 | 0.00 | 0.00 | 10.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
    | 3 | aims | 7 cents per minute | 30 | 6 | 0.00 | 0.00 | 60.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |


    Now when i changed the engine to NDB the table looked like follows:

    SELECT * FROM PLANTF

    +--------+----------+--------------------+-------------+---- --------+------+---------+-------+---------+---------------- +--------------------+---------------------+-----------+---- -----------------+------------+
    | PLANID | PLANNAME | DESCRIPTION | INCREMENTAL | SUBSEQUENT | NRC | PORTNRC | MRC | PORTMRC | CONTRACTPERIOD | EARLYCANCELCHARGES | CREATED | CREATEDBY | MODIFIED | MODIFIEDBY |
    +--------+----------+--------------------+-------------+---- --------+------+---------+-------+---------+---------------- +--------------------+---------------------+-----------+---- -----------------+------------+
    | 1 | tulip | 2 cents per minute | 30 | 6 | 0.00 | 0.00 | 10.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
    | 2 | alfa | 5 cents per minute | 30 | 6 | 0.00 | 0.00 | 10.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
    | 0 | default | default plan | 6 | 6 | 0.00 | 0.00 | 0.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
    | 3 | aims | 7 cents per minute | 30 | 6 | 0.00 | 0.00 | 60.00 | 0.00 | 0 | 0.00 | 2007-01-22 09:26:38 | | 0000-00-00 00:00:00 | |
    +--------+----------+--------------------+-------------+---- --------+------+---------+-------+---------+---------------- +--------------------+---------------------+-----------+---- -----------------+------------+

    Any reasons plz why the order changed with same query.

    Also i want to let u know that the same happened with other tables in my database which are having a column with primary key.

    Plz help
    Thanks in advance

  • #2
    Never relay on order of rows in the table - it is generally undefined unless you use ORDER BY in your Select

    Comment

    Working...
    X