pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedneed magic to shuffle some numbers
3+ messages / 3 participants
[nested] [flat]
* need magic to shuffle some numbers
@ 2011-08-23 19:49 Andreas <maps.on@gmx.net>
0 siblings, 2 replies; 3+ messages in thread
From: Andreas @ 2011-08-23 19:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: need magic to shuffle some numbers
@ 2011-08-24 00:17 Samuel Gendler <sgendler@ideasculptor.com>
parent: Andreas <maps.on@gmx.net>
1 sibling, 0 replies; 3+ messages in thread
From: Samuel Gendler @ 2011-08-24 00:17 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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<http://www.postgresql.org/mailpref/pgsql-sql;
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: need magic to shuffle some numbers
@ 2011-08-24 14:15 Tim Landscheidt <tim@tim-landscheidt.de>
parent: Andreas <maps.on@gmx.net>
1 sibling, 0 replies; 3+ messages in thread
From: Tim Landscheidt @ 2011-08-24 14:15 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-08-24 14:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-23 19:49 need magic to shuffle some numbers Andreas <maps.on@gmx.net>
2011-08-24 00:17 ` Samuel Gendler <sgendler@ideasculptor.com>
2011-08-24 14:15 ` Tim Landscheidt <tim@tim-landscheidt.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox