Re: Reordering a table

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