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(at)industrialsoftworks(dot)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(at)postgresql(dot)org)