By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,177 Members | 976 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,177 IT Pros & Developers. It's quick & easy.

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

P: n/a
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/view...ed/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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.