Solved

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

Posted on 2002-07-22
5
494 Views
Last Modified: 2008-03-06
 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
Comment
Question by:JavaSlave
5 Comments
 

Assisted Solution

by:hdavies
hdavies earned 25 total points
ID: 7169908
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
 
LVL 5

Accepted Solution

by:
spcmnspff earned 25 total points
ID: 7170242
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
 

Author Comment

by:JavaSlave
ID: 7170833
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
 

Expert Comment

by:CleanupPing
ID: 9280084
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now