SQL increment a number based on a column value

[DanHD]

hello

I have a tabele with 3 colums. I need to increment the value of the row with 1 eachtime the partner changes (see column row2)

number partner row row2
1 A100 5 5
2 A100 5 6
3 A100 5 7
4 A200 28 28
5 A200 28 29
6 A300 12 12
7 A300 12 13
8 A300 12 14
9 A300 12 15

I tried: SELECT cardcode, linenum, ROW_NUMBER() OVER(PARTITION BY cardcode ORDER BY cardcode DESC) AS "Row Number" from TEST02
But then tow2 start each time by 1
Someone an idea?
I've tried

regards
Dan



Dan

 

[drlex]

I'm confused by your code not corresponding to your example data.
However, using the latter:

; with cte as (Select 1 as [number], 'A100' as [partner], 5 as [row]
Union all select 2,'A100', 5
Union all select 3 ,'A100', 5
Union all select 4 ,'A200', 28
Union all select 5 ,'A200', 28
Union all select 6 ,'A300', 12
Union all select 7 ,'A300', 12
Union all select 8 ,'A300', 12
Union all select 9 ,'A300', 12
)

select *, row-1+ROW_NUMBER() OVER(PARTITION BY [partner] ORDER BY [partner],[number] ASC) AS [Row2]  from cte

soi là, soi carré

 

[DanHD]

hello

I'm sorry, I've tried my own code also on another database.
The example data is correct. But there are over 3000 records. I think the union is not a realistic option in this case.
I've tried partition by, while, a cursur. But maybe there is a much better solution for this.



Dan

 

[gk53]

From your first post you have a table with data right?
drlex use union to just create a virtual table ti show you result. All what you need is

select *, row-1+ROW_NUMBER() OVER(PARTITION BY [partner] ORDER BY [partner],[number] ASC) AS [Row2] from yourtable

 

[DanHD]

hi

this works, super!
both thanks

Dan

Dan