Date: Tue, 27 Jan 2004 01:15:05 +0100
From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-sql@postgresql.org
Subject: how to "enumerate" rows ?
Message-ID: <20040127011505.H606@hermes.hilbert.loc>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.3.22.1i
Re-X-turn-receipt-to: Karsten.Hilbert@gmx.net
Di-X-sposition-Notification-To: Karsten.Hilbert@gmx.net

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