[TriLUG] Any SQL gurus willing to help?

[Andrew Perrin]

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