pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhow to "enumerate" rows ?
2+ messages / 2 participants
[nested] [flat]
* how to "enumerate" rows ?
@ 2004-01-27 00:15 Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 1 reply; 2+ messages in thread
From: Karsten Hilbert @ 2004-01-27 00:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: how to "enumerate" rows ?
@ 2004-01-27 01:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2004-01-27 01:08 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-01-27 01:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-27 00:15 how to "enumerate" rows ? Karsten Hilbert <Karsten.Hilbert@gmx.net>
2004-01-27 01:08 ` Tom Lane <tgl@sss.pgh.pa.us>
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