Re: What can I use as a [non-aggregate] minimum function

I knew I shouldn't have cut corners.  More specifically, I have tables with
date ranges, for example:

  --table company--
ssn         company    employ_from    employ_to
123456789   whiznet    1999-01-01     2000-06-30

  --table hmo--
ssn         hmo        enroll_from    enroll_to
123456789   goodhealth 1999-01-01     1999-07-31
123456789   careplan   1999-08-01     2000-06-30

  --table ira--
ssn         broker     member_from    member_to
123456789   bigbroker  1999-01-01     1999-12-31
123456789   tinybroker 2000-01-01     2000-06-30

I want to get:

ssn         company        hmo          broker      from        to
123456789   whiznet        goodhealth   bigbroker   1999-01-01  1999-07-31
123456789   whiznet        careplan     bigbroker   1999-08-01  1999-12-31
123456789   whiznet        careplan     tinybroker  2000-01-01  2000-05-30

The idea is to smash all the tables together (by ssn), then keep the
records where the dates from each table share at least one common day, then
pick the max start date, and min end date, but the max and min are the
stickler.

Since you didn't volunteer "just use not_aggregate_max()", I'm assuming
there's no built-in thing, so the follow-up question(s) would be:
  1) can a function be defined with a variable number of arguments
  2) can a function be created that calls itself (i.e. recursive)
  3) can you do it for me? :)

Thanks,

-paul


hmmm... nice name I seem to have set for myself...
> "=?iso-8859-1?Q?Paul_Wehr?=" <postgresql@industrialsoftworks.com>
> writes:
>> I need to find the minimum of dates in a number of tables, but
>> "min(date)"  is, of course, an aggregate function.  For example:
>
>> select key, min(a.date, b.date, c.date) as first_date
>> from table_a a, table_b b, table_c c
>> where a.key=b.key and a.key=c.key
>
> Does that really express the computation you want, ie produce a result
> only for key values that occur in all three tables?
>
> I was going to suggest
>
>select key, min(date) as first_date from
> (select key, date from table_a
>  union all
>  select key, date from table_b
>  union all
>  select key, date from table_c) subsel
> group by key;
>
> but it occurs to me that this produces different results, ie, it will
> include key values that only occur in one or two of the tables ...
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 2: you can get off all lists
> at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
>     majordomo@postgresql.org)