Re: need magic to shuffle some numbers

(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