How to create a counter (Row number) in UDF ?

 I want to create a counter in UDF. The counter I mean :

"SELECT counter(), field1 FROM table1"..
The result is :
1  ,  data1
2  ,  data2
3  ,  data3
4  ,  data4
5  ,  data5

Please give me brief steps info about it. Thank you so much!! :)
JavaSlaveAsked:
Who is Participating?
Advertise Here
 
spcmnspffConnect With a Mentor Commented:
If your table has some unique field in it (like a customerid), a ranking query will perform this in one step:

Select (Select Count(*) From Table Where UniqueField > T1. UniqueField) ID, *
from Table T1

Or if you're planning on inseting your data into a table anyway, the identity function is pretty handy with a select into statement:

select Identity(int,1,1) ID, *
into Table2
From Table

Select * from table2
0
 
hdaviesConnect With a Mentor Commented:
how about a two-step answer.

create a temp table with an identity field, then add into it the results you are interested in, then select from thr temp table

eg
CREATE TABLE #result (id int identity, field1...)

INSERT #result
SELECT field1 FROM table1

SELECT *
FROM #result
0
 
JavaSlaveAuthor Commented:
Thank you.

But I hope to do the effect in UDF. Without modify or write complicated SQL statement. Please give me some related examples. Thank.
0
 
CleanupPingCommented:
JavaSlave:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.