[TriLUG] Any SQL gurus willing to help?
kevin at kreamer.org
Mon Dec 17 14:01:10 EST 2007
I haven't tried it, but something like below should work:
SET final = true
WHERE NOT EXISTS
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.
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!
> Andrew J Perrin - andrew_perrin (at) 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