How to find Duplicate records from table?.

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to find Duplicate records from table?.

Author

Topic

umapathy
Starting Member

24 Posts

Posted - 2009-12-22 : 04:02:33

Hi,

I have a table. it contains n number of records.I need to list out duplicate records from the table.

Ex;

Name Age
x 10
y 20
z 30
x 10
x 10

Result should be

Name Age
x 10
x 10

Please reply to my query

Thanks
Umapathy

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-22 : 04:22:44

Hi

Try this..


CREATE TABLE #DUP([NAME] VARCHAR(1),AGE INT)

INSERT INTO #DUP
SELECT 'X', 10 UNION ALL
SELECT 'Y' ,20 UNION ALL
SELECT 'Z' ,30 UNION ALL
SELECT 'X' ,10 

Method 1

SELECT * FROM #DUP WHERE [NAME] IN
(
SELECT [NAME] FROM #DUP GROUP BY [NAME],AGE HAVING COUNT(*)>1 
)

Method 2

SELECT * FROM #DUP
GROUP BY [NAME],AGE
HAVING COUNT(*) > 1

Method 3

;With CTE AS
( 
	SELECT ROW_NUMBER() OVER(PARTITION BY [NAME] ORDER BY [NAME]) As RowID, 
		[NAME], 
		AGE 
	FROM #DUP 
) 
SELECT * 
FROM	CTE 
WHERE	RowID > 1

-------------------------
R...

Sachin.Nand

2937 Posts

Posted - 2009-12-22 : 08:14:00

[code]

Method 4

SELECT MAX(nm),age FROM #DUP D1
INNER JOIN
(
SELECT (name) AS nm FROM #DUP
)T ON D1.NAME=T.nm
GROUP BY age

[/code]

PBUH

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-22 : 08:18:00

Hi

I think Umapathy not yet accept our solutions.

-------------------------
R...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources