By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,255 Members | 1,701 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,255 IT Pros & Developers. It's quick & easy.

Create a dynamically incremented field

P: 5
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
Feb 18 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Feb 18 '08 #2

P: 5
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
Feb 19 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Isolate your problem. Try your query without the outer WHERE first, which would probably look something like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT X,Y,
  2. (SELECT COUNT(*)
  3. FROM dbo.tbldo e2
  4. WHERE e2.doid <= dbo.tblDO.doID) AS rank
  5. from dbo.tblDO 
  6. 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
Feb 19 '08 #4

P: 5
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
Feb 19 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.