Order of rows in MySql query

[groovyhippo]

If I have a table that is only ever added to (i.e. I use insert and update queries, but not delete queries), then will the order of the rows in the table reflect the order they were added? Or is it not that simple?

[eaden]

Quite often the order is of the inserted order, but I have seen cases where it doesn't happen. I'm not sure why.

But you can make an auto_increment field which means that whenever you insert a new row, that field will be the last auto_increment value, +1. You can then order by this number.

[Storyteller]

groovyhippo, mark each inserted record with the time of addition. In MySQL, just add a field of type TIMESTAMP. auto_increment column will also work, but it's very different semantically and therefore might not be such a good idea.

[groovyhippo]

Thanks for your replies. I had considered adding an auto_increment or timestamp field, but would prefer not to make any changes to the table.

It's actually not critical that the rows I retrieve are in order, but it would be *nice*.

I'm trying to display a list of all 'widgets' available and would like to display them in reverse order of their addition to the site. If I can count on them being returned pretty much in the correct order with the odd out-of-place entry then I'd be happy with that. Do you think that's a safe assumption?

[Storyteller]

Probably, not. Some MySQL tools (like REPAIR TABLE, etc) don't care to preserve the insertion order. There's even an option to change it on purpose.

[groovyhippo]

Fair enough. I hadn't realised before that TIMESTAMP gets updated automatically, so I think I'll go with that solution as it doesn't involve any extra coding.

Thanks for your help.

[grahamstewart]

Don't rely on things being in the right order in the table, instead use the ORDER BY clause in the select.

e.g.

select name, type from widgets order by added_date

[jaski]

Order is not (guaranteed to be) preserved.
The use of auto_increment or timestamp field depends on

1) If you want to order by last insert it has to be auto_increment.

2) If you want to order by last insert OR update .. it has to be through a timestamp field.

either way there will be no extra coding required except that ORDER BY clause in your sql query.

HTH
Jaski

[BCMG_Scott]

FYI - with a TIMESTAMP field, the date/time will change if you update the record to the update time. So if you want to preserve the insert date this may not be the best idea.

Scott Geiger

[jatar_k]

Good point Scott,

Either make it a DATE type or you can also use two timestamp fields. Both are set on insert and only the first is affected on update. Though if you alter the table they will all get changed (learned that long ago the hard way).

[waldemar]

For those cases, I am always using two field "dtmCreated" and "dtmModified".

groovy: The change will not have any destructive effect on already existing data. See www.juicystudio.com/tutorial/mysql/alter.html for table manipulation details.

[edited by: jatar_k at 6:55 pm (utc) on June 10, 2003]

[edited by: waldemar at 7:46 pm (utc) on June 10, 2003]

[Birdman]

Or you could make the new field an INT and use the PHP time() [us2.php.net] function and then ORDER BY that field. This way it doesn't get updated, unless you want it to.

[dingman]

How about just defining the column as 'colname DATETIME not null default current_timestamp', and *never* altering that column when you do an UPDATE? I know it works with Postgres, except that there you'd use a TIMESTAMP type. I haven't needed it in anything where I was stuck with MySQL yet, but it seems straightforward enough.

[BCMG_Scott]

In MySQL TIMESTAMP is a "special" type. If you have that col type in your table the first column of that type will automatically be updated - regardless of whether you include it in the update SQL or not, and regardless of any constraints. I prefer the unix_timestamp() method on an int(11) column - easier to do math if you need to.

Scott

[groovyhippo]

Well, it seems there's more than one way to skin a cat...

I went for just adding a TIMESTAMP field to the table. As updates are very infrequent, and it isn't absolutely crucial that they are in exact order, that seemed like the best solution and involved minimal extra coding.

Thanks for all your help.

[dingman]

In MySQL TIMESTAMP is a "special" type. ...

Yup, hence the fact that my suggested snippet used DATETIME. I just said that in Postgres, where it doesn't have that odity, I'd use TIMESTAMP.