From: Karsten Hilbert <Karsten.Hilbert@gmx.net>
To: pgsql-sql@postgresql.org
Subject: how to "enumerate" rows ?
Date: Tue, 27 Jan 2004 01:15:05 +0100
Message-ID: <20040127011505.H606@hermes.hilbert.loc> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Karsten.Hilbert@gmx.net
Subject: Re: how to "enumerate" rows ?
In-Reply-To: <20040127011505.H606@hermes.hilbert.loc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox