Create a dynamically incremented field

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)

Create a dynamically incremented field

Author

Topic

flumonion
Starting Member

4 Posts

Posted - 2008-02-19 : 02:11:35

Hi,

Does anyone know how i could add a incrementing field to a select statement.

at the moment i have the following:

SELECT X,Y,
(SELECT COUNT(*)
FROM dbo.tbldo e2
WHERE e2.doid <= dbo.tblDO.doID) AS rank
from dbo.tblDO INNER JOIN
dbo.tblMA_XY ON dbo.tblDO.doID = dbo.tblMA_XY.doID INNER JOIN
dbo.tblMA_MA ON dbo.tblMA_XY.MA_MAIN_ID = dbo.tblMA_MA.MA_MAIN_ID
WHERE (dbo.tblMA_XY.purchaseprice > 357000) and (dbo.tblMA_XY.purchaseprice < 500000)
ORDER BY rank

the problem with this statement is that the rank is not sequential because the id i am using in my subquery is not sequential.

for example the results look like this:

X Y rank
-34.020559 18.348137 1
-34.041561 18.369392 1452
-34.035796 18.361704 2470
-34.036907 18.361067 2488
-34.041291 18.369034 2910
-34.040666 18.368614 2946
-34.040167 18.35322 3272
-34.036984 18.363736 4768

instead of:

X Y rank
-34.020559 18.348137 1
-34.041561 18.369392 2
-34.035796 18.361704 3
-34.036907 18.361067 4
-34.041291 18.369034 5
-34.040666 18.368614 6
-34.040167 18.35322 7
-34.036984 18.363736 8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 04:06:06

Can you please post your table structure and some sample data. that will help us in giving you quick and accurate solution.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 07:28:32

If you are trying to generate serial number for the resultset and if you use front end application, do numbering there. Otherwise move the result to temp table which has identity column

Madhivanan

Failing to plan is Planning to fail

flumonion
Starting Member

4 Posts

Posted - 2008-02-19 : 08:27:41

thanks for the advice.

I ended up using a temp table to store the returned records and then a neat little trick i found to add a increment, which is:

declare @intCounter int
set @intCounter = 0
update #Temp
SET @intCounter = rank = @intCounter + 1

thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 09:25:00

Try
COUNT(DISTINCT doID)

instead of COUNT(*)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources