From: Rod Taylor <pg@rbt.ca>
To: Matthew Nuzum <cobalt@bearfruit.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: most efficient way to manage ordering
Date: Tue, 01 Jun 2004 23:10:55 -0400
Message-ID: <1086145854.85942.77.camel@jester> (raw)
In-Reply-To: <200406020235.i522Zrnb015405@ms-smtp-02.tampabay.rr.com>
References: <200406020235.i522Zrnb015405@ms-smtp-02.tampabay.rr.com>
> Ideally, I'd like to figure out a single SQL query that can be run
> afterwards to clean up the dsply_order to make sure that each number occurs
> only one time and that there are no gaps.
Well... by far the easiest way to approach this is not to clean up the
gaps. Removing gaps will only make things pretty, not easier or faster.
This is one of the many times it is best to differentiate between what
is displayed and what is used for functional purposes.
CREATE TEMPORARY SEQUENCE images_display_count;
SELECT nextval('images_display_count') AS display_order
, *
FROM images
WHERE galleryid = 1
ORDER BY real_order;
DROP SEQUENCE images_display_count;
There are ways of replacing the sequence that may be faster, but this
will address your concern. Do your updates, etc. via real_order and show
the user display_order.
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: pg@rbt.ca, cobalt@bearfruit.org
Subject: Re: most efficient way to manage ordering
In-Reply-To: <1086145854.85942.77.camel@jester>
* 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