From: | Alberto Cabello Sánchez <alberto(at)unex(dot)es> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Secret Santa List |
Date: | 2015-12-23 07:59:52 |
Message-ID: | 20151223075941.GA2606@marmota.unex.es |
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 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.
Of course: you can't UPDATE a field with a query returning more than one
result, as you can check easily trying:
update secretsanta set recipient=(select giver from secretsanta);
You could get a list of givers in no particular order (e. g. "select giver
from secretsanta order by md5(concat(giver,current_time))") then setting
each employee as next's employee giver.
--
Alberto Cabello Sánchez
Universidad de Extremadura
From | Date | Subject | |
---|---|---|---|
Next Message | George Neuner | 2015-12-23 08:13:06 | Re: Shared system resources |
Previous Message | David Rowley | 2015-12-23 04:35:31 | Re: Secret Santa List |