-1

I need to add a column for a row number to let the user adjust the row position manually.

The table already contains some data and has a timestamp column containing date and time of when the row was inserted.

The question is how to initialize the row number column using the same sequence defined by the timestamp ordering so that records added earlier would have lower row number.

Ivan
  • 63,011
  • 101
  • 250
  • 382
  • not sure if I understand request completely. I would create a temp table having an autoincremental column,a timestamp column and if timestamp have duplicated entries a row identifier. Then, insert all timestamps from your table in ascending order. Now you can join by timestamp (and row identifier) for update yoe new column – Horaciux Sep 30 '14 at 23:54
  • This idea has came into my mind too, @Horaciu, but I am curious if there is a prettier way. Really, isn't it possible just to add a counter column to query? – Ivan Sep 30 '14 at 23:58
  • http://stackoverflow.com/questions/4829400/adding-id-auto-increment-after-table-exist – Horaciux Oct 01 '14 at 00:06

1 Answers1

0

You could try the following;

SELECT @row_number:=@row_number+1 AS row_number, timestamp FROM table, (SELECT @row_number:=0) AS t
ORDER BY timestamp;
phicon
  • 3,549
  • 5
  • 32
  • 62