how to "enumerate" rows ?

I sent this to pgsql-general first but eventually figured this
list is the more appropriate venue for asking for help.

If this happens to be a FAQ item please briefly point me where
to go or what search terms to use in the archive.

> 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