[TriLUG] Any SQL gurus willing to help?

[Kevin Kreamer]

I haven't tried it, but something like below should work:

UPDATE attempts
SET final = true
WHERE NOT EXISTS
  (SELECT 1
  FROM attempts a2
  WHERE attempts.phone_num = a2.phone_num
  AND a2.attempt_when > attempts.attempt_when);

I do need to point out that I'd be concerned about database normalization
with respect to a final column, given how you've laid out the tables here.
Just FYI.

Kevin


On 12/17/07, Andrew Perrin <clists at perrin.socsci.unc.edu> 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/
>