Subject: Re: most efficient way to manage ordering
From: Rod Taylor <pg@rbt.ca>
To: Matthew Nuzum <cobalt@bearfruit.org>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200406020235.i522Zrnb015405@ms-smtp-02.tampabay.rr.com>
References: <200406020235.i522Zrnb015405@ms-smtp-02.tampabay.rr.com>
Content-Type: text/plain
Message-Id: <1086145854.85942.77.camel@jester>
Mime-Version: 1.0
Date: Tue, 01 Jun 2004 23:10:55 -0400
Content-Transfer-Encoding: 7bit

> 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.