Solved

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

Posted on 2002-07-22
5
511 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 37
Return Rows as per Quantity of Columns Value In SQL 6 27
SQL Server Express or Standard? 5 25
Running Total Using new MS SQL Function 21 50
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

756 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