Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
michaeleisenstein
Starting Member
4 Posts |
 Posted - 2011-05-11 : 09:04:50
|
| HiI have a table that goes like this:FIELD1 FIELD2 FIELD3PATIENTA 01/01/2010PATIENTA 01/02/2010PATIENTA 01/03/2010PATIENTB 01/04/2010PATIENTB 01/05/2010PATIENTB 01/06/2010The table is ordered by FIELD1, FIELD2 ASCWhat I would like to do is create a function that is run that numbers the record in a sequence and places that number in field 3.The sequence is based on FIELD1 and FIELD2 and the order they are in.When the PATIENT changes it must then go ahead and number the records from the start again depending on the order of FIELD1 and FIELD2 for a specific patient.So once the function has been run the data in the table needs to look like this:FIELD1 FIELD2 FIELD3PATIENTA 01/01/2010 1PATIENTA 01/02/2010 2PATIENTA 01/03/2010 3PATIENTB 01/04/2010 1PATIENTB 01/05/2010 2PATIENTB 01/06/2010 3Thanks in advance. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-05-11 : 09:09:13
|
[code]row_number() over (partition by FIELD1 order by FIELD2)[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-05-11 : 09:22:26
|
Just an add on:quote:
The table is ordered by FIELD1, FIELD2 ASC
No, a table isn't reliable ordered!You always have to use ORDER BY in your SELECT statement to get your result set ordered.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
michaeleisenstein
Starting Member
4 Posts |
Posted - 2011-05-11 : 09:29:22
|
| Hi guys, thank you for responses. Got it to work 100% for my needs based on help. Thank you. |
|
|
|
|
|
|
Create a function in SQL to number rows