Insert rownums in mysql table via query or script

Question

Hey!

I have a table that has 5 fields and has a total of 400 records. I added a new field which I called 'order' which, as the name suggests, is to keep an order in the records.

Since I added this after the records had been inserted I was wondering if there was a way to populate this field with the current order of the records using something like rownum via a sql query or do I need to use a PHP script to do the insert?

PS: I tried googling for rownum and didn't find anything useful :(

Answer 1

ROWNUM is Oracle specific syntax. MySQL doesn't have a ranking function - the closest you can get is to use:

SELECT t.*, 
       @rownum := @rownum+1 'rownum'
  FROM TABLE t, (SELECT @rownum := 0) r

...but that will only number the rows - it won't reset when you cross a group, you'll have to query a group at a time.

Answer 2

I don't think it is possible in SQL but you can use your primary key as initial order. After all, by default rows are sorted based on it anyway.

UPDATE table SET order = id;