[TriLUG] Any SQL gurus willing to help?

[Don Jerman]

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

Sorry I'm late - but this caught my eye on the last working day before
the holiday :)

You should be able to use IN instead of = in your statement above
(i.e. select phone_num, max... returns the subset of rows for update,
so test if 'this' row is in it).

However, ideally you should create the subset in your analytic query
so that you never have to depend upon this calculated field (that's
why the normalization rule: calculated fields should be calculated,
not stored).  I don't know if postgres would melt down with this
syntax, but you could just join to the subquery if it were Oracle (not
selling Oracle, just that's what SQL dialect I play with all day).

So something like

Select a.blah_blah_blah, b.blah_blah from surveys a join
    (select c.blah_blah from attempts c where
           (c.phone_num, c.attempt_when) in
              (select d.phone_num, max(d.attempt_when) from
                 attempts d group by d.phone_num)
     ) b
     on a.phone_num = b.phone_num
     order by a.blah_blah_blah

Where a.blah_blah_blah is whatever survey data you want and
b.blah_blah is whatever attempt data you need to join with it.  b is a
subquery that generates the set of records that would have been "final
= true", and gets joined with a to produce the desired result.  You
might not have to specify the c and d aliases but I did it to keep the
references clear.

That approach makes for big query text but it gives the db engine all
the clues about how to make it work quickly and guarantees that you
didn't forget to update the "final" field.