Create a dynamically incremented field

[flumonion]

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

[ck9663]

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

I saw this technique somewhere in the web. I have not tried this myself. But by just looking at your query, I don't think whether the ID you're using is sequential or not. I think the reason you're getting those value for your RANK field is because of your INNER JOIN. It's filtering out those records that are not on those two tables that you're "INNER-JOINING" it with.

Good luck.

-- CK

[flumonion]

I saw this technique somewhere in the web. I have not tried this myself. But by just looking at your query, I don't think whether the ID you're using is sequential or not. I think the reason you're getting those value for your RANK field is because of your INNER JOIN. It's filtering out those records that are not on those two tables that you're "INNER-JOINING" it with.

Good luck.

-- CK

hi there,

i am not too sure about that for this reason:

X Y rank
-34.020559 18.348137 1
-34.041561 18.369392 1452
-34.035796 18.361704 2470

if you look at the rank the second row has an id that is 1451 bigger that the previous id. The subqeury say "bring back a count of all ids that are less than or equal to the current id"

Am i mistaken?

Thanks
Chris

[ck9663]

Isolate your problem. Try your query without the outer WHERE first, which would probably look something like this:

SELECT X,Y,
(SELECT COUNT(*)
FROM dbo.tbldo e2
WHERE e2.doid <= dbo.tblDO.doID) AS rank
from dbo.tblDO 
ORDER BY rank

Analyze the result if it's what you're trying to do. If it gives the proper then add the first INNER JOIN and the related WHERE. Check the result, again. Observe how your resultset are being reduced by the filter.

Happy coding.

-- CK

[flumonion]

Hi CK,

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