From: Ben K. <bkim@coe.tamu.edu>
To: pgsql-sql@postgresql.org
Subject: Re: Most efficient way to hard-sort records
Date: Sun, 7 May 2006 09:16:41 -0500 (CDT)
Message-ID: <Pine.GSO.4.64.0605070837330.12629@coe.tamu.edu> (raw)
In-Reply-To: <op.s8590fficigqcu@apollo13>
References: <445C5A94.4020907@startnet.cz>
<Pine.GSO.4.64.0605061507500.7602@coe.tamu.edu>
<op.s8590fficigqcu@apollo13>
> 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
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
Subject: Re: Most efficient way to hard-sort records
In-Reply-To: <Pine.GSO.4.64.0605070837330.12629@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