Updating a sql table with new column

Question

I am using mysql and I have a table with columns: postid, userid, datestamp

I want to create a new column called 'entry' that numbers the entries chronologically according to datestamp

So, for the rows:

3705    1   2003-12-08 13:42:13
3711    15  2003-11-12 15:22:01
3701    2   2004-01-11 01:22:12

I want to end up with:

3705    1   2003-12-08 13:42:13 2
3711    15  2003-11-12 15:22:01 1
3701    2   2004-01-11 01:22:12 3

How can I perform this task?

Answer 1

ALTER TABLE tbl ADD COLUMN nr integer;

SET @rn := 0;

UPDATE tbl
SET    rn = (@rn := @rn + 1)
ORDER  BY datestamp, postid, userid;

Here is a working demo.

I took the substitute for the missing window function row_number() in MySQL from @OMG Ponies' Posting.
Sort by postid and userid in addition to datestamp as the timestamp column is not guaranteed to be unique.
Read about setting variables in the manual.

Answer 2

ALTER TABLE tbl ADD COLUMN entry INTEGER

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

For setting it to the ordered number you would use RANK function but MySQL doesn't have one, so will have to look for substitutes like this one: http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/

Answer 3

If you want just to order chronologically the dates you dont have to create another column. You can do it with a simple cursor.

private static String[] FROM = {_ID, COL_1, COL_NAME, COL_2, COL_3, COL_4}; //add your columns
private static String ORDER_BY = COL_NAME + " DESC"; //or ASC for ascending
Cursor cursor = db.query(DATABASE_TABLE, FROM, null, null, null, null, ORDER_BY);

and then just show the results from the cursor