Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL increment a number based on a column value

Status
Not open for further replies.

DanHD

Technical User
Dec 1, 2008
98
NL
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
 
I'm confused by your code not corresponding to your example data.
However, using the latter:
Code:
; 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é
 
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
 
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
 
hi

this works, super!
both thanks

Dan

Dan
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top