Re: Secret Santa List

On Tue, Dec 22, 2015 at 9:49 PM, Lou Duchez <lou@paprikash.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');
> --

with
  g as (select giver, row_number() over () as rownum from secretsanta),
  r as (select giver, row_number() over () as rownum from (select
giver from secretsanta order by random()) as x)
update secretsanta
  set recipient = r.giver
  from g join r on g.rownum = r.rownum
  where secretsanta.giver = g.giver;

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company