[TriLUG] Any SQL gurus willing to help?

[Andrew Perrin]

On Mon, 17 Dec 2007, Kevin Kreamer wrote:

> 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);

That's very similar to what I eventually came up with - thanks.

>
> 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.
>

An excellent point, and one I should consider before moving forward....

Andy

> 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/
>>
> -- 
> 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/
>


----------------------------------------------------------------------
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