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

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