From: Ben K. <bkim@coe.tamu.edu>
To: Miroslav Šulc <miroslav.sulc@startnet.cz>
Cc: pgsql-sql@postgresql.org
Subject: Re: Most efficient way to hard-sort records
Date: Sun, 7 May 2006 01:53:46 -0500 (CDT)
Message-ID: <Pine.GSO.4.64.0605061507500.7602@coe.tamu.edu> (raw)
In-Reply-To: <445C5A94.4020907@startnet.cz>
References: <445C5A94.4020907@startnet.cz>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bkim@coe.tamu.edu, miroslav.sulc@startnet.cz
Subject: Re: Most efficient way to hard-sort records
In-Reply-To: <Pine.GSO.4.64.0605061507500.7602@coe.tamu.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox