iterating through variable in sql where statement

Question

I've got this kind of statement:

declare @max int
@max = 1
SELECT @max, t1.col1, t1.col2
FROM table1 t1

this will give me results:

1 a a
1 b b
1 c c

and I would like to get this kind of result

1 a a
2 b b
3 c c

how can I achieve this result?

I've tried to do as follow:

@max = 1
SELECT @max, t1.col1, t1.col2
FROM table1 t1
WHERE @max = @max + 1

but with no success, can someone help me with it? thanks!

PS. I have to use @max as variable - I can not use Identity or AUTOINCREMENT column

score 7 · Accepted Answer · 2013-10-17 12:46:53Z

7

Use the row_number() function.

SELECT row_number() over (order by t1.col1, t1.col2),t1.col1, t1.col2
FROM table1 t1

Starting from a fixed value :

declare @your_number int
set @your_number = 24353

SELECT @your_number + row_number() over (order by t1.col1, t1.col2) AS num,t1.col1, t1.col2
FROM table1 t1

edited Oct 17, 2013 at 12:46

answered Oct 17, 2013 at 12:16

user2869880

Sign up to request clarification or add additional context in comments.

1 Comment

Krystian Over a year ago

ok, but assuming that I dont want to start from 1... but for ex. from some random int 24353? how can I do that?

gzaxx · Accepted Answer · 2013-10-17 12:19:32Z

1

Try this:

with cte as 
(
  SELECT t1.col1, t1.col2, ROW_NUMBER() by (order by t1.col1, t1.col2) as RowNumber
  FROM table1 t1
)

select c.RowNumber, c.col1, c.col2
from cte c

row_number() function will return row number starting from 1.

answered Oct 17, 2013 at 12:19

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

Collectives™ on Stack Overflow

iterating through variable in sql where statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related