Date: Sun, 7 May 2006 01:53:46 -0500 (CDT)
From: "Ben K." <bkim@coe.tamu.edu>
To: =?ISO-8859-2?Q?Miroslav_=A9ulc?= <miroslav.sulc@startnet.cz>
cc: pgsql-sql@postgresql.org
Subject: Re: Most efficient way to hard-sort records
In-Reply-To: <445C5A94.4020907@startnet.cz>
Message-ID: <Pine.GSO.4.64.0605061507500.7602@coe.tamu.edu>
References: <445C5A94.4020907@startnet.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

> 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