From: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
---|---|
To: | Lou Duchez <lou(at)paprikash(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Secret Santa List |
Date: | 2015-12-23 04:35:31 |
Message-ID: | CAKJS1f_pqwAygRfxyjQOY3r+Y4OMuRaG_UXfLR2-gNOA26jt-w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 23 December 2015 at 16:49, Lou Duchez <lou(at)paprikash(dot)com> wrote:
> I have a company with four employees who participate in a Secret Santa
> program, where each buys a gift for an employee chosen at random. (For
> now, I do not mind if an employee ends up buying a gift for himself.) How
> can I make this work with an SQL statement?
>
> Here is my Secret Santa table:
>
> --
> create table secretsanta
> (giver text,
> recipient text,
> primary key (giver));
>
> insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'),
> ('Earl');
> --
>
> Here is the SQL statement I am using to populate the "recipient" column:
>
> --
> update secretsanta set recipient =
> ( select giver from secretsanta s2 where not exists (select * from
> secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );
> --
>
> The problem: every time I run this, a single name is chosen at random and
> used to populate all the rows. So all four rows will get a recipient of
> "Steve" or "Earl" or whatever single name is chosen at random.
>
Why not generate the required results in a SELECT then update from that.
row_number() could allow you to generate a random number to each giver,
then we can generate another random number and join to each random number.
That'll give you a giver and recipient combination.
e.g:
select giver,recipient from
(select row_number() over (order by random()) rn, giver from secretsanta) g
inner join
(select row_number() over (order by random()) rn, giver recipient from
secretsanta) r on g.rn = r.rn
You can then wrap that up in a CTE, something along the lines of:
with cte (giver, recipient) as (
select giver,recipient from
(select row_number() over (order by random()) rn, giver from secretsanta) g
inner join
(select row_number() over (order by random()) rn, giver recipient from
secretsanta) r on g.rn = r.rn
)
update secretsanta set recipient = cte.recipient from cte WHERE cte.giver =
secretsanta.giver;
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Alberto Cabello Sánchez | 2015-12-23 07:59:52 | Re: Secret Santa List |
Previous Message | Lou Duchez | 2015-12-23 03:49:52 | Secret Santa List |