Обсуждение: need magic to shuffle some numbers
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
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.on@gmx.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
(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