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
writingsome 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