Finding duplicate entries. - SQL Server Forums

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

Finding duplicate entries.

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-13 : 10:52:38

In the following query:
Select ARRESTNO, LNAME, FNAME, DOB from ARR_PER order by ARRESTNO
Select ARRESTNO, LNAME, FNAME, dob from ARREST order by ARRESTNO
Select ARRESTNO from ARR_AFSS order by ARRESTNO
I need to have the result set for the first two tables show me only the results which have rows with duplicate arrestno's, ordered by the arrestno's. What changes do i need to the above to show me that? Again, only for the first two tables.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 10:58:25

do you mean this?


SELECT ARRESTNO, LNAME, FNAME, DOB
FROM
(
Select ARRESTNO, LNAME, FNAME, DOB,COUNT(1) OVER (PARTITION BY ARRESTNO) AS Cnt 
from ARR_PER 
)t
WHERE Cnt>1
order by ARRESTNO


select ARRESTNO, LNAME, FNAME, dob
from
(
Select ARRESTNO, LNAME, FNAME, dob,COUNT(1) OVER (PARTITION BY ARRESTNO) AS Cnt 
from ARREST 
)t
WHERE Cnt >1
order by ARRESTNO

or this?


SELECT ARRESTNO, LNAME, FNAME, DOB
FROM
(
Select ARRESTNO, LNAME, FNAME, DOB ,1 AS Cat
from ARR_PER
UNION ALL
Select ARRESTNO, LNAME, FNAME, dob,2 
from ARREST 
)t
GROUP BY ARRESTNO, LNAME, FNAME, DOB
HAVING COUNT(DISTINCT Cat) =2
order by ARRESTNO

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-13 : 11:03:12

Hard for me to say. I ran both. The first provided me with two resultsets, which is what i want to "see", but the second resultset had no data, which could mean i don't have duplicate arrestnos in that table, or it's not doing what i want. The second query produced only one resultset, which is not what i need to see, but i assume that what it's doing is placing the values from both tables into the one resultset via the UNION. Let me dig at it more to see if the first query is showing me the results i suspect.
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 11:22:21

quote:
Originally posted by WJHamel

Hard for me to say. I ran both. The first provided me with two resultsets, which is what i want to "see", but the second resultset had no data, which could mean i don't have duplicate arrestnos in that table, or it's not doing what i want. The second query produced only one resultset, which is not what i need to see, but i assume that what it's doing is placing the values from both tables into the one resultset via the UNION. Let me dig at it more to see if the first query is showing me the results i suspect.
thanks

No problem
Run them against sample data and then determine what you need

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-13 : 11:34:26

It is certain now that the first query is showing me the "truth". Now, based on those results, would the following query work to remove the duplicate entries based on Arrestno from the arr_per table, leaving only one record for each arrestno entry behind?:
SELECT Arrestno,
ROW_NUMBER() OVER(PARTITION BY Arrestno ORDER BY arrestno) AS DuplicateCount
FROM arr_per
)
DELETE
FROM Arr_Per
WHERE DuplicateCount > 1
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 11:37:23

the delete should be like


DELETE t
FROM 
(
SELECT ROW_NUMBER() OVER(PARTITION BY Arrestno ORDER BY arrestno) AS Seq
FROM arr_per
)t
WHERE Seq > 1

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-13 : 11:49:43

Excellent. Now, the next hurdle is to find the entries in the arrest table which have no match in the arr_per table. Will the following query reliably show me this?:
SELECT Arrest.ARRESTNO as [ARREST TABLE], arr_per.arrestno as [ARR_PER TABLE]
FROM Arrest LEFT OUTER JOIN
ARR_PER ON Arrest.ARRESTNO <> ARR_PER.ARRESTNO

Somehow, i don't feel like that is what i should be reaching for.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 11:53:54

nope. you're close


SELECT Arrest.ARRESTNO as [ARREST TABLE], arr_per.arrestno as [ARR_PER TABLE]
FROM Arrest LEFT OUTER JOIN
ARR_PER ON Arrest.ARRESTNO = ARR_PER.ARRESTNO
WHERE ARR_PER.ARRESTNO IS NULL


or


SELECT Arrest.ARRESTNO as [ARREST TABLE]
FROM Arrest 
WHERE NOT EXISTS(SELECT 1 FROM ARR_PER WHERE Arrest.ARRESTNO = ARRESTNO)

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-13 : 12:23:25

Second query is what i needed. Thanks for setting me straight. Lastly, i'm now needing to create entries in the arr_per table, based on the above data from the Arrest table. Initially, i thought what i would be doing is specifying the fields that i need to copy from the arrest table (i.e., update arr_per, set Arrestno=arrest.arrestno WHERE NOT EXISTS(SELECT 1 FROM ARR_PER WHERE Arrest.ARRESTNO = ARRESTNO)]
DOB=arrest.DOB where not exists....
SSN=.........
and so on)

However, is there a way for me to create entries in the arr_per table where they exist in the Arrest table only, and bring over any and all fields for those records which match between the two tables? If so, it seems it would be less convoluted than writing the extended Update/Set script.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 12:28:21

[code]
UPDATE ARR_PER
SET ARR_PER.DOB=Arrest.DOB,
...
FROM Arrest
INNER JOIN ARR_PER
ON Arrest.ARRESTNO = ARR_PER.ARRESTNO

INSERT INTO ARR_PER (DOB,...)
SELECT DOB,... FROM Arrest
WHERE NOT EXISTS(SELECT 1 FROM ARR_PER WHERE Arrest.ARRESTNO = ARRESTNO)
[/code]

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

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-13 : 12:47:34

That's what i was afraid of. So there's no way to say 'Insert into arr_per * where columnname=columname and where arrestno does not exist' ?, without having to spell out each column name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 13:32:17

nope...no way other than specifying column list. you can generate the column list before hand using information_schema.columns view though

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

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-13 : 14:33:09

and not only that

you DON'T want to do that even if you could..

What would happen if the table structure changed?

You are a .NET Developer right?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-12-13 : 14:37:34

Visakh: Thanks. I used the following to show me the columns in common between the two tables:

select b.name AS [Field Name] from sysobjects a, syscolumns b
where b.id = a.id and a.name='ARREST' and a.xtype = 'U' and b.name IN (SELECT b.name FROM sysobjects a, syscolumns b WHERE b.id=a.id AND a.name='arr_per' AND a.xtype='U')

Brett: Understood, but this was a one-off update.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:19:40

quote:
Originally posted by WJHamel

Visakh: Thanks. I used the following to show me the columns in common between the two tables:

select b.name AS [Field Name] from sysobjects a, syscolumns b
where b.id = a.id and a.name='ARREST' and a.xtype = 'U' and b.name IN (SELECT b.name FROM sysobjects a, syscolumns b WHERE b.id=a.id AND a.name='arr_per' AND a.xtype='U')

Brett: Understood, but this was a one-off update.

ok
I prefer INFORMATION_SCHEMA.COLUMNS as its a view that contains columnname,tablename etc together

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources