By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

how to enumerate rows ?!?

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.