Check for duplicate names with same code value.

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

General SQL Server Forums

New to SQL Server Programming

Check for duplicate names with same code value.

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-10 : 14:21:41

I have a table with the following columns

Offenseno
Code
Lname
Mname
Fname
ECSOID
Uniquekey

I need to look for rows where the CODE value AND the Offenseno is the same AND the Fname, Mname, and Lname values are the same. If there are two or more rows meeting this criteria, i need to remove those rows (first moving them to another table) and leave only ONE row with that CODE, Fname, Mname, and Lname value.

Example

[Code]

Offenseno Code Lname Mname Fname ECSOID Uniquekey
DCSO12OFF123456 S Smith Sam Bill DCSO11MNI ajh$%d&
DCSO12OFF123456 S Smith Sam Bill DCSO12MNI a^^#7d&
DCSO12OFF123456 S Smith Sam Bill DCSO12MNI _+hd7d&
DCSO12OFF123456 S Smith Sam Bill DCSO12MNI ajh#*d&
DCSO12OFF123456 V Cruz Jen Crystal DCSO88MNI aj456d&
DCSO12OFF123456 V Hoffman Bud Tom DCSO78MNI ajhuyd&
DCSO12OFF123456 V Stryker Jess Bud DCSO44MNI mnld7d&
DCSO12OFF123456 WC Cruz Jen Crystal DCSO88MNI tehd7d&
DCSO12OFF123456 WC Cruz Jen Crystal DCSO88MNI pohd7d&

[/code]

So in the example above, i would need to identify the rows which have the same Offenseno, same Code, same Fname, mname and lname, then leave only ONE row. All rows must have the same offenseno, so they should be first grouped by that. A row can have the same FNAME, MNAME, and LNAME if the CODE differs, but if the code is the same, only one row can be left in place. The ECSOID may or may not vary between persons of the same name. It's really included just to be able to do a visual check.

So once i've identified these rows, i need to move them into a new table with a select into query and then i need to delete from offense.dbo.offense those same rows so only one remains.

Am i making sense? If so, what is suggested?

Thanks

James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 14:32:29

[code]

INSERT NewTable
SELECT Offenseno,
Code,
Lname,
Mname,
Fname,
UniqueKey
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Offenseno,
Code,
Lname,
Mname,
Fname ORDER BY UniqueKey) AS Seq,*
FROM table
)t
WHERE Seq > 1

DELETE t
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Offenseno,
Code,
Lname,
Mname,
Fname ORDER BY UniqueKey) AS Seq,*
FROM table
)t
WHERE seq>1[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-10 : 14:34:33

Visakh,

I thought about that, but what would be the difference between that and this:


Delete D FROM
(
SELECT Offenseno, Code, Lname, Mname, Fname, ROW_NUMBER()OVER(ORDER BY Offenseno, Code, Lname, Mname, Fname) AS RowNum 
FROM dbo.BOTHWARRANTFILES)D
JOIN
(
SELECT Offenseno, Code, Lname, Mname, Fname, ROW_NUMBER()OVER(ORDER BY Offenseno, Code, Lname, Mname, Fname) AS RowNum 
FROM dbo.BOTHWARRANTFILES
)E
on D.Offenseno = E.Offenseno
AND D.RowNum < E.RowNum
AND D.Code=e.Code 
AND d.Fname=e.Fname
AND d.Mname=e.Mname
AND d.Lname = e.lname

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-10 : 15:03:34

If you run them side by side, you'll see that the delete different things. MOre accuraly, you'll see they attempt to delete a different number of rows. So, the join method appears to work, but you would be trying to delete the same physical row more than once:

DECLARE @Foo TABLE
(
    Offenseno VARCHAR(20), 
    Code VARCHAR(10),
    Lname VARCHAR(20),
    Mname VARCHAR(20),
    Fname VARCHAR(20),
    ECSOID VARCHAR(20),
    Uniquekey VARCHAR(20)
)
INSERT @Foo
VALUES
('DCSO12OFF123456', 'S',      'Smith',    'Sam',      'Bill',     'DCSO11MNI', 'ajh$%d&'),
('DCSO12OFF123456', 'S',      'Smith',    'Sam',      'Bill',     'DCSO12MNI', 'a^^#7d&'),
('DCSO12OFF123456', 'S',     'Smith',    'Sam',      'Bill',     'DCSO12MNI', '_+hd7d&'),
('DCSO12OFF123456', 'S',      'Smith',    'Sam',      'Bill',     'DCSO12MNI', 'ajh#*d&'),
('DCSO12OFF123456', 'V',      'Cruz',     'Jen',      'Crystal',  'DCSO88MNI', 'aj456d&'),
('DCSO12OFF123456', 'V',      'Hoffman',  'Bud',      'Tom',      'DCSO78MNI', 'ajhuyd&'),
('DCSO12OFF123456', 'V',      'Stryker',  'Jess',     'Bud',      'DCSO44MNI', 'mnld7d&'),
('DCSO12OFF123456', 'WC',     'Cruz',     'Jen',      'Crystal',  'DCSO88MNI', 'tehd7d&'),
('DCSO12OFF123456', 'WC',    'Cruz',     'Jen',      'Crystal',  'DCSO88MNI', 'pohd7d&')



--Delete D 
SELECT *
FROM
(
SELECT Offenseno, Code, Lname, Mname, Fname, ROW_NUMBER()OVER(ORDER BY Offenseno, Code, Lname, Mname, Fname) AS RowNum 
FROM @Foo)D
JOIN
(
SELECT Offenseno, Code, Lname, Mname, Fname, ROW_NUMBER()OVER(ORDER BY Offenseno, Code, Lname, Mname, Fname) AS RowNum 
FROM @Foo
)E
on D.Offenseno = E.Offenseno
AND D.RowNum < E.RowNum
AND D.Code=e.Code 
AND d.Fname=e.Fname
AND d.Mname=e.Mname
AND d.Lname = e.lname 


--DELETE t
SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Offenseno,
Code,
Lname,
Mname,
Fname ORDER BY UniqueKey) AS Seq,*    
FROM @Foo
)t
WHERE seq>1

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-09-10 : 15:14:14

Got it. Thanks again guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 15:30:11

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources