Re: Most efficient way to hard-sort records

>     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