On Feb 22, 2011, at 10:10 PM, Howard Cole wrote: > Hi, > > a puzzle to solve... > > I have a table with a primary key, and a timestamp, e.g. > > id stamp > 1 2011-02-01 10:00 > 2 2011-02-01 09:00 > 3 2011-02-01 11:00 > > Now for reasons too painful to go into, I need to reorder the id (sequence) so that they are in time order: > > id stamp > 1 2011-02-01 09:00 > 2 2011-02-01 10:00 > 3 2011-02-01 11:00 > > I thought I could do it by adding a third colum with the order in it, but I cannot think of a way to do this short of writing some code > > id stamp order > 1 2011-02-01 10:00 2 > 2 2011-02-01 09:00 1 > 3 2011-02-01 11:00 3 > > Any ideas? > If you are using PG 8.4 then you can try something with row_number as given below: select id,stamp, row_number() over(order by stamp) from test; Or Create table test1 as select row_number() over(order by stamp) as id, stamp from test; Thanks & Regards, Vibhor Kumar -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
