December 5, 2009 at 12:30 pm
cos_ta393 (12/2/2009)
Good Afternoon,I am working with a dataset that has duplicate ID's due to unique records. The challenge is any number of column(s) can cause the uniqueness therefore there is no way to issolate a single column. The goal is to drop all dups but one and it doesnt matter which records is left.
Below is a sample dataset.
Thanks,
CREATE TABLE [dbo].[DupsDueToUniqueRows](
[ID] [int] NULL,
[Name] [nvarchar](30) NULL,
[Contact] [nvarchar] (30) NULL,
[Status] [char] (1) NULL
) ON [PRIMARY]
INSERT INTO [DupsDueToUniqueRows] ([ID], [Name], [Contact], [Status])
SELECT 1, 'Jon' ,'Mobile', 'A'
UNION ALL
SELECT 1, 'Jon' ,'Mobile', 'B'
UNION ALL
SELECT 1, 'Jon' ,'Pager', 'A'
UNION ALL
SELECT 2, 'Paul' ,'Mobile', 'A'
UNION ALL
SELECT 2, 'Paul' ,'Mobile', 'B'
UNION ALL
SELECT 3, 'Art' ,'Mobile', 'B'
UNION ALL
SELECT 3, 'Art' ,'Mobile', 'A'
UNION ALL
SELECT 4, 'Mike' ,'Mobile', 'A'
UNION ALL
SELECT 4, 'Mike' ,'Pager', 'A'
UNION ALL
SELECT 4, 'Mike' ,'Mobile', 'B'
UNION ALL
SELECT 4, 'Mike' ,'Cell', 'A'
UNION ALL
SELECT 5, 'Art' ,'Mobile', 'A'
GO
Heh... I've seen everyone's solution and I have to ask... using the data from above, what would you actually like to see as a return?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 6, 2009 at 7:02 am
Jeff,
What I really need is a solution that deletes the duplicate records. As I explained the dups are occurring because of uniqueness in the rows caused by any number of columns on the row. The actual table has over a hundred columns therefore I can't isolate just one. At this point the solution should just keep one of the records and delete all the others.
Thanks
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
December 6, 2009 at 9:12 am
I'm just having a hard time with your description... what you really mean is that if the ID is duplicated, it's considered to be a dupe and you don't care which rows you delete for that ID so long as you just end up with one row for that ID, correct?
Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 6, 2009 at 9:48 am
Correct. Also please note that you can have more than two occurrances of the ID.
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy