Re: Re: keeping order of insertion. - Carlos Paz - com.mysql.lists.mysql

10 messages in com.mysql.lists.mysqlRe: Re: keeping order of insertion.

From	Sent On	Attachments
Carlos Paz	16 May 1999 17:30
Paul DuBois	16 May 1999 18:44
Carlos Paz	18 May 1999 14:38
Thimble Smith	18 May 1999 14:50
Pete Harlan	18 May 1999 14:57
Carlos Paz	18 May 1999 16:23
Stephen F. Johnston Jr.	18 May 1999 17:50
Dan Nelson	19 May 1999 08:02
Michael Widenius	22 May 1999 01:45
Michael Widenius	22 May 1999 02:22

Subject:	Re: Re: keeping order of insertion.
From:	Carlos Paz (cap...@colomsat.net.co)
Date:	05/18/1999 02:38:19 PM
List:	com.mysql.lists.mysql

Hi there folks!

I´m stuck in a situation that requires to keep the order of insertion of the data. I tried to do this with mysql, but unfortunately it keeps fetching the rows in no particular order. I´m aware there is a common workaround, but I wouldn´t like to add a row and sort it (unnecesary overhead), and I might be wrong, but I think that 'one of the rules' in a database system is to respect the insertion sequence of data if no particular order is specified.

Why do you think that?

Does any relational database guarantee such a property?

Well I don't remember where, but I learned that a long time ago. I've seen that work with my first sql database (a DB2's grandma, running on IBM 360 machines an PL1), Postgres, Access and even Mysql(using "insert into table_x from select...", but It doesn't work well with "insert into table_x values...") I think that this works too in Oracle and SQL Server but I haven't tested it.

Anyway, here is a paragraph from "Teach Yourself SQL in 21 Days" (It's the only book that I've checked so far) :

"From time to time you will want to present the results of your query in some kind of order. As you know, however, SELECT FROM gives you a listing, and unless you have defined a primary key (see Day 10, "Creating Views and Indexes"), your query comes out in the order the rows were entered." - Extracted from Day 5 : Clauses in SQL, Order from Chaos: The ORDER BY Clause

I wonder if I can trust on this feature with "insert into x select .." and expect "insert into x values..." compliment in newer releases or I'll need to use the extra column workaround ?

Thanks again...