April 11, 2005 at 9:32 am
I was wondering if there is a way to create a new IDENTITY column that would keep the same identity value for each duplicate row. for example, say I had five rows with the same telephone number, I would want those rows to contain the same identity value, is there anyway of doing this?
thanks
April 11, 2005 at 10:27 am
You won't be able to use IDENTITY property for this. IDENTITY, by definition automatically increments when a new row is added to a table. You'll have to assign identification numbers programatically.
Greg
Greg
April 11, 2005 at 12:21 pm
Do you have any insights as to where I might be able to read up on programing such a task? There is quiet a few records and would be a pain to go through and assign each number a unique value.
April 11, 2005 at 12:26 pm
You're pretty much asking for an omelette that's eggs free. You cannot have a unique value that is not unique to all rows.
What r u trying to achieve by adding the identity column to that table?
April 11, 2005 at 3:05 pm
well I guess that would be a pretty crapy omelette. and I know what your getting at. But what I'm trying to do here is figure out a way to automatically assign a unique value to a grouping of records. take this for example:
| NAME | NUMBER | IDENT |
| JTL GROUP INC | 247-2540 | 1 |
| JTL GROUP INC | 247-2540 | 1 |
| JTL GROUP, INCORPORATED | 247-2540 | 1 |
| JTL GROUP INC | 252-8465 | 2 |
| JTL GROUP, INC. | 255-7126 | 3 |
| JTL GROUP INC | 867-2000 | 4 |
| JTL GROUP INC | 867-2000 | 4 |
| M G MIDWEST INC | 222-5228 | 5 |
| M G MIDWEST INC | 222-5228 | 5 |
| M G MIDWEST INC | 222-5228 | 5 |
See how there are some phone Numbers that appear more than once, I want to give them the same idententy value for grouping purposes, because the Name, and other values change, so I need a unique value to that one Number. Thats all i'm trying to do, can't quite figure it out yet
April 11, 2005 at 3:14 pm
This will do what you want, but without the identity (all the values will be unique but many numbers will be skipped).
Select Distinct C.name, (Select count(*) from dbo.SysColumns C2 where C2.name <= C.Name) as Ident from dbo.SysColumns C order by C.name
You could do something like :
Update Main set YourIdentCol = (Select count(*) from dbo.YourTable Sub where Sub.name <= Main.Name) from YourTable Main
April 12, 2005 at 8:01 am
This data does not seem normalised. Are you keeping it this way for ease of reporting ?
Why not create a separate table with an IDENTITY column and all the distinct company name/telephone number combinations from your original table. Then encode the data in the orginal table with the value from the new table, and remove the original data.
If it ain't broke, don't fix it...
April 12, 2005 at 8:34 am
oh its normalized, i'm doing a data clean up/data mining project, that information is some of the columns from my joined view that I work off of. the TranTypeName use to just say, Sale, Renewal, Cancellation, Disconnect but I used CASE and the getdate to determine if the sales and renewals were still in contract and gave them their new names.
Great, thanks for the help guys, both ideas will work. back to trying to sort this mess of a database out, take it easy.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply