Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Detroit
    Posts
    11

    Unanswered: INSERT INTO...order not preserved

    Im fairly new to databases, and facing this problem:

    I find that mySQL is not able to preserve the way in which we write data into the database. How can I correct this problem, as I think its something to do with my definition of Keys in the Table.

    Ex: if I inser values (10, 1) ,(20, 4), (5, 1) and ask the mySQL to print it out, then it prints them not quiet in the same order I entered.

    Thanks,
    DeepakRam

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: INSERT INTO...order not preserved

    Originally posted by deepakram
    Im fairly new to databases, and facing this problem:

    I find that mySQL is not able to preserve the way in which we write data into the database. How can I correct this problem, as I think its something to do with my definition of Keys in the Table.

    Ex: if I inser values (10, 1) ,(20, 4), (5, 1) and ask the mySQL to print it out, then it prints them not quiet in the same order I entered.

    Thanks,
    DeepakRam
    There is no thing such as "database order". if you want to sort data, use the ORDER BY clause... If you want to sort data based on the time it was inserted into the database, add a timestamp column and order your result based on that column.

  3. #3
    Join Date
    Oct 2003
    Location
    Detroit
    Posts
    11

    Re: INSERT INTO...order not preserved

    [Thanks for the reply. But I am not still convinced how to achieve this.
    Suppose, I need to store the following fields in my table:
    (10, 1), (9, 4), (5, 3) and whant them stored excatly in the same order.
    even if i create an additional column in my table to accomodate, say a timestamp field, what is the guarentee that mySQL will write these values excatly in the same order, and not change (for optimisztions) ?

    I hope you undersdtand my point.
    Thanks,

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: INSERT INTO...order not preserved

    Originally posted by deepakram
    [Thanks for the reply. But I am not still convinced how to achieve this.
    Suppose, I need to store the following fields in my table:
    (10, 1), (9, 4), (5, 3) and whant them stored excatly in the same order.
    even if i create an additional column in my table to accomodate, say a timestamp field, what is the guarentee that mySQL will write these values excatly in the same order, and not change (for optimisztions) ?

    I hope you undersdtand my point.
    Thanks,
    You cannot *store* the data in any particular order... It's kept in a B+ tree! You can *retrieve* it in a particular order, but not *store* it...

    in your case, you could use an auto-increment or a datetime column and order by that extra column... For instance, let's say you declare your table like:

    CREATE TABLE mytest (
    col1 int,
    col2 int,
    col3 int not null auto_increment);

    insert into mytest (col1, col2, col3) values (10, 1, NULL);
    insert into mytest (col1, col2, col3) values (9, 4, NULL);
    insert into mytest (col1, col2, col3) values (5, 3, NULL);

    You could then issue:

    select * from mytest order by col3 asc;

    Hope this helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •