Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.
Course Of The Month
3 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
How to create a counter (Row number) in UDF ?
Posted on 2002-07-22
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!! :)
"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!! :)
[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
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
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
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
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
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.
But I hope to do the effect in UDF. Without modify or write complicated SQL statement. Please give me some related examples. Thank.
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.
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Error "Could not load mobile report" in SQL SSRS 2016 after SP1 CU2 Update | 2 | 43 | |
| When are cursors useful? | 8 | 62 | |
| Populate fields in group based on first record in group | 4 | 16 | |
| SQL Server and Access Database (Project Codes) get the next record | 43 | 35 |
Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month3 days, 17 hours left to enroll
751 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.