max() over some rows but grouped, within one view def - how ? - Mailing list pgsql-general

Hi,

I would appreciate any help on the following problem:

Suppose I have a table (simplified) (vacc = vaccination)

vacc_def
    fk_indication
    seq_no
    age_due_min
    age_due_max
    ...

where seq_no gives the sequence number of a particular
vaccination event definition.

Now, if I want to find the last scheduled vaccination for the
known indications I can run this query:

 select fk_indication, max(seq_no) from vacc_def group by fk_indication;

This works as expected.

I now want to create a view with a column is_last_shot
that is TRUE where
    seq_no = (
    select max(seq_no)
    from vacc_def
    where fk_indication = <some indication PK>
    )

Obviously, one would use a CASE construct to set the (virtual)
column is_last_shot to either true or false depending on the
value of seq_no compared to max(seq_no) for that
indication. However, how do I know <some indication PK> in
the view definition ?!?

Another possibility would be to use UNION to aggregate the
queries per fk_indication but that means one needs to know the
fk_indication values at view creation time which isn't
technically sound.

Or do I have to resort to writing a plpgsql function employing
a LOOP construct ?

I can post the full table/view defs and data if needed or you
can find them here:

http://savannah.gnu.org/cgi-bin/viewcvs/gnumed/gnumed/gnumed/server/sql/

-> gmclinical.sql   (tables)
-> gmClinicalViews.sql    (views)
-> gmClinicalData.sql    (data)
-> country.specific/de/STIKO-Impfkalender.sql    (more data)

Thanks,

Karsten Hilbert, MD
(www.gnumed.org)

PS: Yes, I did order "SQL for Smarties" courtesy of this
list's suggestion ;-)
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346