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 |
|
Chinwa
Starting Member
20 Posts |
 Posted - 2004-10-18 : 11:35:53
|
| I need a way to simply add a column to a record set returned from a select statement that lists the record number of each returned record.Like:userid fname lname record number0012 John Smith 10092 Sally Jones 20001 Bob TheVerb 30201 Bob Thenoun 4The 1,2,3,4 are the record numbers. They are not keys and do not currently exist.I do not know if a function will work because I do not know if I can delcare a variable in a function that will retain its value from the last time it was called (STATIC)I was hoping for something simple like @RECORDNUMBER but can't find anything like that.Any suggestions? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-18 : 11:43:59
|
will this do?select t1.userid, t1.fname, t1.lname, rank=count(*)from MyTable t1, MyTable t2where cast(t1.userId as varchar(50)) + t1.fname + t1.lname >= cast(t2.userId as varchar(50)) + t2.fname + t2.lnamegroup by t1.userid, t1.fname, t1.lnameorder by 4Go with the flow & have fun! Else fight the flow  |
 |
|
|
|
|
|
Creating my own sort field or record number