Insert number to column - Good One....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert number to column - Good One....

Author

Topic

mayurcreation
Starting Member

16 Posts

Posted - 2007-11-14 : 11:42:26

Hi everybody..

I have good challenging question for you. I look simple but when you try you will come to know the complex.

View : View_FundsOutput
rownum, portfolio_code, security_code, lot_number
1, AA, 012245, null
2, AA, 012245, null
3, AA, 012245, null
4, AA, 012245, null
5, AA, 585652, null
6, AA, 585652, null
7, AA, 895985, null
8, AA, 895985, null
9, BB, 012245, null
10, BB, 012245, null
11, BB, 012245, null
12, BB, 565895, null
13, BB, 789654, null
14, BB, 147852, null
15, BB, 147852, null
16, BB, 147852, null
17, BB, 085265, null
....

I need to allocate lot_number in incremental formate
Query should first take portfolio_code into account and than security_code.
e.g.
portfolio_code, security_code, lot_number
AA 012245 1
AA 012245 2
AA 012245 3
AA 585652 1
AA 585652 2
AA 895985 1
AA 895985 2
.....
BB 012245 1
BB 012245 2
BB 012245 3
BB 789654 1
BB 789654 2
BB 789654 3
....

Any input will be highly appreciated.....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-14 : 11:45:17

it can be easily done with the row_number() function. Look it up in Books On Line

KH
[spoiler]Time is always against us[/spoiler]

mayurcreation
Starting Member

16 Posts

Posted - 2008-01-09 : 11:51:03

Answer to above problem is as follows:-

update View_FundsOutput set lot_number = q.new_num
from View_FundsOutput vf, (SELECT portfolio_code, security_code, rownum, ROW_NUMBER() OVER(PARTITION BY portfolio_code, security_code ORDER BY rownum DESC) As new_num FROM view_fundsoutput) as q
where vf.rownum = q.rownum

Thanks for your suggestions.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-10 : 01:41:13

and instead of having it as seperate column, derive it in a SELECT statement

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources