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.
| Author |
Topic |
|
original_caro
Starting Member
6 Posts |
 Posted - 2010-05-05 : 12:58:16
|
| hi all,i have a table A with columns name, age and city, with sample records like:name age citypaul 15 new yorkmaria 18 salt lakemaria 18 salt lakeroy 17 Massachusettsroy 17 Massachusettsroy 17 MassachusettsPaul 15 Wisconsin and an another table B same as A with an extra column rank.table B should be inserted from A with rank field sequenced as shown belowname age city rankpaul 15 new york 1maria 18 salt lake 1maria 18 salt lake 2 roy 17 Massachusetts 1 roy 17 Massachusetts 2 roy 17 Massachusetts 3Paul 15 Wisconsin 1 i have come up against a blank trying to do this. the constraint for generating rank is,first time three columns are same in table A then rank is to be 1 and every time same set of records occur the rank is to be increased by 1.Regards,carro |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-05-05 : 13:26:11
|
| [code]select row_number () over(partition by name,age,city order by name) as rank,* from TableA[/code] |
 |
|
|
original_caro
Starting Member
6 Posts |
Posted - 2010-05-06 : 01:35:27
|
| thanks a lot Vijay, it worksRegards,carro |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-05-06 : 10:02:32
|
| You're welcome. |
|
|
|
|
|
|
generating sequence