Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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