Re: Most efficient way to hard-sort records

> main_table: id, name, position
> key_table: id, main_table_id, key, value
>
> Here is how I need to sort the records:
> SELECT * FROM main_table
> INNER JOIN key_table ON main_table.id = key_table.main_table_id
> WHERE key = 'param'
> ORDER BY value
>
> I currently collect all ids from main_table in sorted order and then
> update the position field for each row in the main_table one-by-one. Is
> there a better/faster/more efficient solution?


A cheap solution if you don't care about the position value as long as 
sort order is ok.

1)
# SELECT main_table.id into temp_table FROM main_table INNER JOIN 
key_table ON main_table.id = key_table.main_table_id ORDER BY value;

2)
# update main_table set position = (select oid from temp_table where id = 
main_table.id );

I guess I'll get a set of consecutive oids by this.

You can make the number begin at arbitrary number, by

2-a)
# update main_table set position = ( (select oid::int4 from temp_table 
where id = main_table.id ) - (select min(oid::int4) from temp_table) + 1) 
;

I read that oid wraps around (after ~ billions) so you might want to check 
your current oid.




Regards,

Ben K.
Developer
http://benix.tamu.edu