Re: Reordering a table

Something like:

SELECT
ordered.stamp,
nextval('sequence') AS rownumber
FROM (SELECT stamp FROM table ORDER BY stamp ASC) ordered

Incorporate the ID field and UPDATE as necessary to get the result the way
you need it.

You are apparently aware that you likely have a design or understanding
issue due to the fact that you need to do this persistently.

You may be able to accomplish a similar result without the use of a sequence
by using WINDOW but for a one-off scenario the sequence should suffice.

David J.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Howard Cole
Sent: Tuesday, February 22, 2011 11:41 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Reordering a table

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?

Thanks.
Howard
www.selestial.com

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general