[TriLUG] Any SQL gurus willing to help?

[Jeremy Portzer]

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

I agree that adding a column like this isn't the "best" way to do things 
based on the normalization rules.  It seems that just using the latest 
attempt_when should be sufficiient, and this column isn't needed at all. 
However as long as the final column is being created, it might be a good 
idea to set up a trigger that resets these columns when a new survey 
attempt is added.   Otherwise it could be easy for this to get "out of 
date" making other queries that depend on it return wrong results.

--Jeremy