CREATE TABLE sorted (order_no SERIAL PRIMARY KEY, other columns...)
INSERT INTO sorted (columns) SELECT * FROM main_table INNER JOIN
key_table ON main_table.id = key_table.main_table_id WHERE key = 'param' ORDER
BY value SELECT
The SERIAL will automatically generate the order_no you want, which
corresponds to the position in the sorted set.
Then, to get the records in-order :
SELECT * FROM sorted ORDER BY order_no
Good ... I just got myself into the habit of not recreating a table since
I have to clean up permissions and what not. I guess it depends.
Another version along that line ?
# create sequence counterseq start 1;
-- (set/reset whenever a counter is needed)
# select main_table.*, nextval('counterseq') as position2
into sorted_main_table
from main_table, keytable where main_table.id =
keytable.main_table_id
order by value;
Regards,
Ben K.
Developer
http://benix.tamu.edu
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
