Обсуждение: 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

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
>> 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 ...

The best, recommended way to do this is to plaster on the row numbers
in your client-side code.  AFAIK there just isn't any way to do it in
standard SQL.

If you feel you really gotta have a server-side solution, the easiest
way is
CREATE TEMP SEQUENCE myseq;
SELECT nextval('myseq'), *  FROM (SELECT ... ORDER BY date_given) ss;
DROP SEQUENCE myseq;

(Alternatively, you can create a temp sequence once per session and just
reset it with setval() for each query.)

Note that you can *not* simplify this to
SELECT nextval('myseq'), ... ORDER BY date_given;

because if you do, the nextval() values may get computed before the
ORDER BY sorting occurs.  Putting the ORDER BY into a sub-select makes
it work reliably.

This is not real portable since neither sequences nor ORDER BY in a
subselect are SQL-standard.  It's also probably a good deal slower than
a simple client-side counter, because nextval() does a lot more than
just add one to a variable.  But if you have to have it, there it is.
        regards, tom lane