• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

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!! :)
0
JavaSlave
Asked:
JavaSlave
2 Solutions
 
hdaviesCommented:
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
 
spcmnspffCommented:
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
 
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now