Home » SQL & PL/SQL » SQL & PL/SQL » numbering different records of each patient (oracle 9)
numbering different records of each patient [message #357072] Mon, 03 November 2008 15:08 Go to next message
schoenif
Messages: 1
Registered: November 2008
Location: Switzerland
Junior Member
Dear all
I have a table with patient id and several laboratory date per patient (from one to several dozens). I would like to give to each records per patient a observation number in ascending order of the laboratory date. How can I store this in a variable obs.

Need urgent help!!!! Confused I would like to do this with PL/SQL

table looks like this (total of 9000 records):
ID laboratory date obs
112 date1 1
112 date2 2
112 date3 3
115 date1 1
115 date2 2
115 date3 3
115 date4 4
115 date5 5
115 date6 6
Re: numbering different records of each patient [message #357094 is a reply to message #357072] Mon, 03 November 2008 22:18 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
How can I store this in a variable obs


I am assuming OBS is field name in the table and it is not a variable and you need to update this field in the table.

If my assumption is correct then I suggest you need to read documentation on row_number() analytical function and merge command to achieve this.


Updated: Forum guidelines prohibits in providing solution directly so did not provide direct solution to you.

[Updated on: Mon, 03 November 2008 22:20]

Report message to a moderator

Re: numbering different records of each patient [message #357115 is a reply to message #357072] Tue, 04 November 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: numbering different records of each patient [message #357126 is a reply to message #357115] Tue, 04 November 2008 01:26 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Study This

I don't know how many times i have to post this link.

Please Search before posting next time.

Regards,
Rajat Ratewal
Previous Topic: How do I know if I can truncate a table?
Next Topic: Oracle error
Goto Forum:
  


Current Time: Thu Nov 07 23:50:53 CST 2024