[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