Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to fetch duplicate records Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 3:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 11:10 PM
Points: 5, Visits: 30
Hi,

I am using SQL Server 2010 with Visual Studio for my application. Now I have a table say "EmEmployeesand that have some 5000 records. This table has a column "Name" which is not a primary field. There is a possibility of more than one records having same name. Now I have to fetch all the records having duplicate names. What should be the efficient way to do this?

Please let me know.

Regards,
Girish
Post #1410438
Posted Wednesday, January 23, 2013 3:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 2,834, Visits: 3,949
girish.nehte-1109699 (1/23/2013)
Hi,

I am using SQL Server 2010 with Visual Studio for my application. Now I have a table say "EmEmployeesand that have some 5000 records. This table has a column "Name" which is not a primary field. There is a possibility of more than one records having same name. Now I have to fetch all the records having duplicate names. What should be the efficient way to do this?

Please let me know.

Regards,
Girish
to get the faster and possible correct help , always post table definition , sampled data plus expected output. see the link in my signature


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1410450
Posted Wednesday, January 23, 2013 3:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 2,834, Visits: 3,949

select name from 
(
select name, row_number() over ( partition by name order by name ) rnk
from EmEmployeesand
) t where t.rnk > 1



-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1410451
Posted Wednesday, January 23, 2013 3:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:49 AM
Points: 5,208, Visits: 9,362
Yes, or use GROUP BY and HAVING. Sounds suspiciously like an interview or homework question to me.

John
Post #1410456
Posted Wednesday, January 23, 2013 4:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:43 AM
Points: 12, Visits: 82
select <field list req'd>
from <your table>
where <field with dupes> in (select <field with dupes> from <your table> group by <field with dupes>
having count(*) > 1)

Post #1410463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse