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 |
|
Brittney10
Posting Yak Master
154 Posts |
 Posted - 2011-02-22 : 13:43:20
|
| I need to create an ID that is grouped by name. And i need to be able to auto increment this ID when a new record is created, or if a new record is created and the name is already in the db, then take the existing id. FYI, this needs to be done to data that is already existing in the db. For example:Name____IDJane..........1Jane..........1Jane..........1Jill.............2Joe............3 Joe............3 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-02-22 : 13:55:30
|
| SELECT [Name],[ID] = dense_rank() over(order by [Name])FROM yourTableJimEveryday I learn something that somebody else already knew |
 |
|
|
Brittney10
Posting Yak Master
154 Posts |
Posted - 2011-02-22 : 14:24:25
|
| How do i insert that ID in my ID columnm, since it's taking 2 columns in the select list, but only one in the insert into/set list? Same can be said for updating a column with the ID.... |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-22 : 15:23:45
|
come up with all details the destination table(s) structure, sample data and the desired insert/update in light of the above example's.  |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-02-22 : 15:30:02
|
quote:
Originally posted by Brittney10
I need to create an ID that is grouped by name. And i need to be able to auto increment this ID when a new record is created, or if a new record is created and the name is already in the db, then take the existing id. FYI, this needs to be done to data that is already existing in the db. For example:Name____IDJane..........1Jane..........1Jane..........1Jill.............2Joe............3 Joe............3
Is that the data you are staring with or the output you want?http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxBut, why would you want to do that? |
|
|
|
|
|
|
Creating ID that is Grouped