Re: need magic to shuffle some numbers

I don't have time to experiment with actual queries, but you can use the
rank() window function to rank rows with prio sorted ascending, and do the
same thing to rank rows with prio sorted descending, and update rows with
the value from the second where the rank matches the rank from the first.
I'm guessing that you can't use a window function in an update directly, so
you'll likely need to structure things as several subselects, but the basic
principle is sound, I think. If you do have more than one row with the same
value, you can use row_number() instead of rank() in order to get unique
'rank' for rows that have the same value.

Window functions are here:
http://www.postgresql.org/docs/9.0/static/tutorial-window.html.

Build a query that returns primary_key1, rownum1, prio1 with prio sorted
ascending. Do the same for rownum2, prio2 with prio sorted descending.
Then join those two queries in an outer query on rownum1 = rownum2. Use
that query as part of an update statement to set prio = prio2 where
primary_key = primary_key1. You can likely do it more efficiently by
combining things into a single query, but you are only going to run this
once, and that was easier to describe, textually.

On Tue, Aug 23, 2011 at 12:49 PM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:

> Hi,
>
> 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.
> ...
>
>
> regards
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql<http://www.postgresql.org/mailpref/pgsql-sql>
>