[TriLUG] Any SQL gurus willing to help?
William Sutton
william at trilug.org
Mon Dec 17 13:39:28 EST 2007
see if this works:
UPDATE attempts a1 SET final=true
WHERE phone_num IN (SELECT DISTINCT phone_num FROM attempts)
AND attempt_when=(SELECT max(attemt_when) FROM attempts a2
WHERE a2.phone_num=a1.phone_num)
(not sure this will work since I haven't actually tested it .... :) )
William Sutton
On Mon, 17 Dec 2007, Andrew Perrin wrote:
> Dear TriLUGgers -
>
> I have a database in PostgreSQL and I need to do an operation that ought
> to be simple, but my SQL is too rusty. Any help would be much appreciated.
>
> I have two tables in the database: one of survey responses, keyed by
> telephone nummber; the other of telephone attempts, also including
> telephone number, but of course with in some cases many attempts per
> telephone number. What I need to do is link the survey responses to the
> attempts table, based on the latest date/time the attempt was made.
>
> I added a column (final) to the attempts table to flag whether this
> attempt was the latest for its phone number. How, though, should I
> populate it? I can get the latest date per phone number thusly:
>
> select phone_num, max(attempt_when) from attempts group by phone_num;
>
>
> ...but my feeble attempts to use that as a base to populate the final
> column have failed:
>
> ncsboe=# update attempts set final = true where (phone_num, attempt_when) = (select phone_num, max(attempt_when) from attempts group by phone_num);
> ERROR: more than one row returned by a subquery used as an expression
>
>
>
> Working through it manually is not an option, as there are 38K attempts to
> 25K phone numbers!
>
> Thanks-
> Andy
>
>
> ----------------------------------------------------------------------
> Andrew J Perrin - andrew_perrin (at) unc.edu - http://perrin.socsci.unc.edu
> Associate Professor of Sociology; Book Review Editor, _Social Forces_
> University of North Carolina - CB#3210, Chapel Hill, NC 27599-3210 USA
>
>
> --
> TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug
> TriLUG Organizational FAQ : http://trilug.org/faq/
> TriLUG Member Services FAQ : http://members.trilug.org/services_faq/
>
>
More information about the TriLUG
mailing list