how to enumerate rows ?!?

[Karsten Hilbert]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)