Re: Order table by record insert order

[Shlomi Noach]

Hi,

Generally, you should not rely on the order of rows returned by the DB when you have not specified any order. So if you've ordered by FilmID, you cannot (or rather, should not) expect an insert order in the Name column.

The basic way to solve this is by using an AUTO_INCREMENT column. Usually, this column is the primary key, by which you retireve "natively" ordered results.
But you can use this like this:
Add an "id", auto_increment field,
then add an index (FilmID, id).
When inserting rows, do not specify value for the id column. This way it will increment, and will therefore retain your insert order.
When retrieving, ORDER BY FilmID, id

Shlomi