Duplicate entries check - 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

Duplicate entries check

Author

Topic

sajanjacobk
Starting Member

7 Posts

Posted - 2009-06-10 : 06:06:05

I need SQL Query to get the primary keys of all the entries which are duplicated.
(or multiple entries in a row of which only PK is unique.)

the fields are
(PK, studentName, Student Address and Roll Number)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 06:09:12

Do you have same sample data and expected output?

E 12°55'05.63"
N 56°04'39.26"

sumitbatra1981
Starting Member

17 Posts

Posted - 2009-06-10 : 06:16:35

This will help u to get the full Duplicate Rows

select * from table1 group by PK, studentName, Student Address,Roll Number having count(*)>1

I m not sure abt the below query for selecting PK only... TRY THIS

select PK from table1 group by PK, studentName, Student Address,Roll Number having count(*)>1

if it doesn't work then u can use

select PK from (select * from table1 group by PK, studentName, Student Address,Roll Number having count(*)>1)

Regards,
Sumit Batra
Software Engineer

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-06-10 : 06:25:00

If StudentName, StudentAddress and RollNumber do not have NULLs you can ditch the COALESCEs to make the following
more efficient:

SELECT *
FROM YourTable T
WHERE EXISTS
(
    SELECT COALESCE(T1.StudentName, ''),  COALESCE(T1.StudentAddress, ''),  COALESCE(T1.RollNumber, '')
    FROM YourTable T1
    WHERE COALESCE(T1.StudentName, '') = COALESCE(T.StudentName, '')
        AND COALESCE(T1.StudentAddress, '') = COALESCE(T.StudentAddress, '')
        AND COALESCE(T1.RollNumber, '') = COALESCE(T.RollNumber, '')
    GROUP BY COALESCE(T1.StudentName, ''),  COALESCE(T1.StudentAddress, ''),  COALESCE(T1.RollNumber, '')
    HAVING COUNT(*) > 1
)
ORDER BY StudentName, StudentAddress, RollNumber

sajanjacobk
Starting Member

7 Posts

Posted - 2009-06-10 : 06:31:14

Thank you,

Sample Data

sample data

PK RollNo Student Name Student Address
1 1122 Ramesh Ramesh House
2 1122 Ramesh Ramesh House
3 1123 John john's House
4 1124 Sita Sita's House
5 1124 Sita Sita's House
6 1125 Geeta Gita's House

I would like to get the result as

PK RollNo Student Name Student Address
1 1122 Ramesh Ramesh House
2 1122 Ramesh Ramesh House
4 1124 Sita Sita's House
5 1124 Sita Sita's House

I need to get the Query so as to delete the multiple entries retaining a single copy in the database table.

In the above case the table after the operation will look like
PK RollNo Student Name Student Address
1 1122 Ramesh Ramesh House
3 1123 John john's House
4 1124 Sita Sita's House
6 1125 Geeta Gita's House

Thank you very much for the faster response.

sumitbatra1981
Starting Member

17 Posts

Posted - 2009-06-10 : 06:37:37

SORRY for a little mistake in earlier post

to delete Duplicate record...and keeeping single record

delete from table1 where PK not in(select min(PK) from table1 group by PK, studentName, Student Address,Roll Number having count(*)>1)

Regards,
Sumit Batra
Software Engineer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-10 : 06:40:13

Sumitra, that will delete ALL records with duplicates.

-- Case 1
SELECT	PK,
	RollNo,
	Student,
	Name,
	StudentAddress
FROM	(
		SELECT	PK,
			RollNo,
			Student,
			Name,
			StudentAddress,
			COUNT(*) OVER (PARTITION BY RollNo, Student, Name, StudentAddress) AS cnt
		FROM	Table1
	) AS d
WHERE	cnt > 1

-- Case 2
DELETE	f
FROM	(
		SELECT	ROW_NUMBER() OVER (PARTITION BY RollNo, Student, Name, StudentAddress ORDER BY PK) AS recID
		FROM	Table1
	) AS f
WHERE	recID > 1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources