consequtive number generation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

consequtive number generation

Author

Topic

magesh
Starting Member

23 Posts

Posted - 2005-09-13 : 05:04:13

Hi all,

i want to have a user defined function to written
line numbers for my query..

i will explain in detail,

iam having set of tables.i will fetch the records using
SQL queries to my reports or form datagrids. now i have to
add one line number column for all these reports and datagrids..

is there any way to create one function and use it with my existing
queries such as to return line numbers or consective numbers together
with result set ..
this hopes to be tricky.

magesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 05:10:03

I think you can easily do this in your Front End application using for or While Loop or in Reports using Record Number

Madhivanan

Failing to plan is Planning to fail

magesh
Starting Member

23 Posts

Posted - 2005-09-13 : 05:28:51

Hi,

you are correct madhi but the problem is

1)iam having my application installed in many PCs
all of them using same SP.
2)there are plenty of reports and forms. and it is tedious
to modify the code instead of altering SP.

is there any way to complete this task in back end itself ?

Magesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 05:37:39

You can do that in back end but if there are millions of records then generating serail number will affect the performance. How many records are there in that table?

Madhivanan

Failing to plan is Planning to fail

magesh
Starting Member

23 Posts

Posted - 2005-09-13 : 07:12:01

Hi Madhi,

My queries will fetch maximum of only 150records
so i hope there wont be any performance issues..

and more over my SPs are performing pretty fastly so
i can bare some millisecs for the DB based approach..

can u give hints abt how to perform this problem in backend itself.

Magesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 07:21:03

Something like this

Select (Select count(*) from yourTable where PrimaryKeyCol<=T.PrimaryKeyCol) as Sno,
otherColumns from yourTable T

Otherwise post table structure with some sample data

Madhivanan

Failing to plan is Planning to fail

magesh
Starting Member

23 Posts

Posted - 2005-09-14 : 01:07:58

hi,

due to security reasons i cant able to post exact table structure.

approx iam selecting 100records from 10000 records table
so in my selected query i want to add line numbers.

more over the selected records is not ordered accroding to primary key.
display order depends on name columns.

atlast i have done this task in front end.
let me know if any one can solve in back end itself.

Magesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-14 : 01:28:29

In the above query replace PrimaryKeyCol by Name and order it by name

Select (Select count(*) from yourTable where Name<=T.Name) as Sno,
otherColumns from yourTable T order by Name

Madhivanan

Failing to plan is Planning to fail

magesh
Starting Member

23 Posts

Posted - 2005-09-14 : 01:58:51

Hi Madhi,

No it wont work in my situation.
ill explain in detail.

i have table of 10,000 records
iam selecting some 100 records based on several search conditions and joins.
my search condition includes name,date etc..

so i framed some what using derived tables. but i held up with errors.

SELECT ( SELECT COUNT(*) FROM
(SELECT colA,colB,colC
FROM table1
INNER JOIN table2 ON colA=colX
WHERE colA=10 AND colB='COLB' ) B
WHERE B.colA < A.COLA AND b.colB < A.colB)
,A.colA,A.colB,A.colC

FROM
(SELECT colA,colB,colC
FROM table1
INNER JOIN table2 ON colA=colX
WHERE colA=10 AND colB='COLB' ) A

the query seems to be little bit worst.

and is there any way to declare a variable globally
such as declaring @@VARA
so that we can increment variable value and use it in Functions to give line numbers

Magesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-14 : 02:04:19

Move the result set to newtable and do query based on that table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources