Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: need magic to shuffle some numbers
Date: Wed, 24 Aug 2011 14:15:50 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 26
Message-ID: <m3vctmlwtl.fsf@passepartout.tim-landscheidt.de>
References: <4E540434.2000603@gmx.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:6smQIh5Lo8ENN4na3GFLC7REc5w=

(anonymous) wrote:

> there is a table that has among others a integer primary key
> "id" and another integer column "prio" as well as an integer
> "group_id".

> I'd like to invert the values of the prio-column for one of the groups.
> The prio numbers start with 3 and there are 1159 different
> prios in this group.
> At least every value appeares only once.   :)

> Is there an elegant way to switch the prio values around so
> that every record with the first prio gehts the last and
> vice versa?
> Then the records with the second smallest prio get the
> second-to-last biggest value and v.v.

If you just want to reverse the priorities:

| UPDATE TestTable
|   SET prio = (SELECT MIN(prio) FROM TestTable WHERE group_id = 'testgroup') +
|              (SELECT MAX(prio) FROM TestTable WHERE group_id = 'testgroup') -
|              prio
|   WHERE group_id = 'testgroup';

Tim