First of all, yes I know that result rows don't have any
intrinsic ordering that I can expect to not change.
I have a table recording vaccinations for patients roughly
like this:
table vaccinations
pk,
patient,
date_given,
disease
Data in that table would look like this:
1,
1742,
2003-11-27
tetanus
3,
1742,
2000-10-24
flu
12,
1742,
2003-1-17
tetanus
Now, I need to enumerate the vaccinations per patient per
disease. They are intrinsically ordered by their date of
vaccination but I need to attach a number to them such that I
have:
#1
tetanus
1,
1742,
2003-11-27
#2
tetanus
12,
1742,
2003-1-17
#1
flu
3,
1742,
2000-10-24
My plan was to select sub-sets by
select
from vaccination
where patient=a_patient_id and disease=a_disease
order by date_given
and then somehow cross (?) join them to a sub-set of the
integer table according to Celko's auxiliary integer table
technique (where I create the integer sub-set table by
select val
from integers
where val <=
select count(*)
from vaccination
where
disease=a_disease and
patient=a_patient
)
But I just can't figure out how to correctly do this...
Note that I try to construct a view and thus don't have
constant values for a_disease and a_patient.
Can someone please point me in the right direction ?
Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
