Query for finding Duplicates - 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

Query for finding Duplicates

Author

Topic

mchandrav
Starting Member

7 Posts

Posted - 2012-11-30 : 12:41:01

Hi All

I have a table with columns Name, Type, ColumnnC1, ColumnnC2, ColumnnC3, ColumnnC4, ColumnnC5. The unique record is identified based on the combination of Name and Type. Now, I need to retrieve duplicate records from this table.
When I run the following query, I get around 200 records which looks okay.

SELECT NAME, COUNT(NAME) AS Total
FROM TABLE
WHERE Type = 'Value1'
GROUP BY NAME
HAVING COUNT(NAME) > 1

But the results count from this query do not match with the above query

SELECT NAME, Type, ColumnnC1, ColumnnC2, ColumnnC3, ColumnnC4, ColumnnC5, COUNT(NAME) AS Total
FROM TABLE
WHERE Type = 'Value1'
GROUP BY NAME, Type, ColumnnC1, ColumnnC2, ColumnnC3, ColumnnC4, ColumnnC5
HAVING COUNT(NAME) > 1

Can someone please help with the second query.

Thanks in advance
M

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-30 : 12:55:14

Think you want
SELECT NAME, Type, ColumnnC1, ColumnnC2, ColumnnC3, ColumnnC4, ColumnnC5, COUNT(NAME) AS Total
FROM TABLE
WHERE Type = 'Value1'
and name in (select name from table where type = 'value1' group by name having count(*)> 1)
GROUP BY NAME, Type, ColumnnC1, ColumnnC2, ColumnnC3, ColumnnC4, ColumnnC5

The having count(name) > 1 doesn't work because the group is distinct over the group by columns not just name.

as name, type are meant to be unique you might want
SELECT NAME, Type, max(ColumnnC1), max(ColumnnC2), max(ColumnnC3), max(ColumnnC4), max(ColumnnC5), COUNT(NAME) AS Total
FROM TABLE
WHERE Type = 'Value1'
GROUP BY NAME, Type
having count(*) > 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-02 : 02:02:04

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY NAME,Type ORDER BY NAME) AS Seq,*
FROM Table
)t
WHERE Seq > 1
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources