how to get only duplicate rows from table

Question

Post reply

how to get only duplicate rows from table

malleswarareddy_m

SSCertifiable

Points: 5847
More actions
January 22, 2010 at 10:16 pm

#136661

the sample table looks like

1 malli o2o2020

2 malli 878hjh8

3 reddy ghhg5

4 reddy 56656

5 eswa ttt656

6 rama ettt

here i want to get only malli and reddy to get which was duplicate.but there was primary key and unique identifier for the same table.how cal i acheive this.Any one appreciated.

i want records exact like this

1 malli o2o2020

2 malli 878hjh8

3 reddy ghhg5

4 reddy 56656

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

did you try to search for a solution already?

If you type "find duplicate rows" in the search box (upper right side of the form) you'll find numerous solutions covering the subject.

We'll be glad to help you if you have trouble understanding how it works.

But I think if we just provide the solution won't help you in the long run...

To know how to look for an already existing solution that just needs to be modified is a programming skill as well...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

Nabha

SSCrazy Eights

Points: 8534

January 23, 2010 at 3:34 am

#1107726

Exactly!!! and moreover.. you are an 'ssc- addicted'. By now you should be knowing how to post data?

---------------------------------------------------------------------------------

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 3

malleswarareddy_m

SSCertifiable

Points: 5847

January 23, 2010 at 5:03 am

#1107732

no that all the article are not working for my request.i want to show all duplicates instead of that.

i already showed the above pattern how ca you achieve this.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

LutzM SSC Guru Points: 107049 More actions · Answer 4

So, which solution did you try with your data and where did you get stuck?

Please provide table definition, sample data, expected result and what you've tried so far as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 5

malleswarareddy_m

SSCertifiable

Points: 5847

January 23, 2010 at 7:01 am

#1107743

Hi ,

i searched the duplcate calls but in my table i have an unique identifier and also i want to dsiplay like this

br_benficiaryname br_village br_id

malli gag 1234

malli gag 1456

so i want o get both records.with diff beneficiaryid

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Hint:

A subquery that groups by whatever column 2 is called and does a filter HAVING COUNT(*) > 1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 7

even i tried like this alo but i have unique identifier for a column name benfiaryid in the above result i want show the unqiue identfier for that colmun.iam not able to got result set .

there are another beneficiary visits table where the beneficaryid is updated

after getting duplicate records we can check which record is updated.so that other records we

want to remove.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

malleswarareddy_m (1/23/2010)

even i tried like this alo but i have unique identifier for a column name benfiaryid in the above result i want show the unqiue identfier for that colmun.iam not able to got result set .

That's why I said a subquery.

Second hint. Join the subquery to the original table

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 9

Strong hint!

Write a query which returns the following result set:

malli 2

reddy 2

- then post it here.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

malleswarareddy_m (1/22/2010)

the sample table looks like

1 malli o2o2020

2 malli 878hjh8

3 reddy ghhg5

4 reddy 56656

5 eswa ttt656

6 rama ettt

here i want to get only malli and reddy to get which was duplicate.but there was primary key and unique identifier for the same table.how cal i acheive this.Any one appreciated.

i want records exact like this

1 malli o2o2020

2 malli 878hjh8

3 reddy ghhg5

4 reddy 56656

Hey there, malleswarareddy_m...

Know why you keep getting non-coded answers to your simple problem? Find out by reading the following article...

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

Here's another example of how to post test data. While I was at it, I also included a solution for you. I still recommend you read the article at the link I posted above for your next post, though.

drop table #testdata



--===== Create the test data

 SELECT *

   INTO #TestData

   FROM (

         SELECT  1,'malli','o2o2020'  UNION ALL

         SELECT  2,'malli','878hjh8'  UNION ALL

         SELECT  3,'reddy','ghhg5'    UNION ALL

         SELECT  4,'reddy','56656'    UNION ALL

         SELECT  5,'eswa','ttt656'    UNION ALL

         SELECT  6,'rama','ettt'      UNION ALL

         SELECT  7,'homer','whatever' UNION ALL

         SELECT  8,'homer','dodah'    UNION ALL

         SELECT 20,'hammer','nails'   UNION ALL

         SELECT 30,'homer','DOH!'

        ) testdata (RowNum, Name, SomeValue);

GO

--===== Solve the problem

WITH

cteDupeName AS

        (

         SELECT Name

           FROM #TestData

          GROUP BY Name

         HAVING COUNT(*) > 1

        )

 SELECT source.RowNum, source.Name, source.SomeValue

   FROM #TestData source

  INNER JOIN cteDupeName dupe

     ON source.Name = dupe.Name

  ORDER BY source.RowNum;

GO

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 11

malleswarareddy_m

SSCertifiable

Points: 5847

January 24, 2010 at 10:04 pm

#1107994

Hi jeff,

yaa your solution is absolutly correct and i found the solution my self and writing the query like your same as your posted query.and this is a simplest solution but i wasted hours of time finally i got solution but i did n't post.

Thanks for all who responds my query.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

malleswarareddy_m (1/24/2010)

...but i wasted hours of time...

So, did you take the time to read the article I pointed you to so that doesn't happen anymore?;-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Saurabh Dwivedy SSCommitted Points: 1885 More actions · Answer 13

Saurabh Dwivedy

SSCommitted

Points: 1885

January 24, 2010 at 11:59 pm

#1108021

Why don't you share your solution with us for our enlightenment?

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 14

this is my query

with Duplicaterecords as

(

select br_beneficiaryname,br_beneficiarysurname,br_fathername,br_dob,br_districtid,br_mandalid,br_villageid,br_servicepointcode,br_beneficiaryid,br_dateofregistration,br_visitdate,

row_number() over(partition by br_districtid,br_mandalid,br_villageid,br_dob,br_beneficiaryname,br_beneficiarysurname,br_fathername order by br_districtid,

br_mandalid,br_villageid,br_beneficiaryname,br_dob,br_servicepointcode) as row from TELEMONITORING.beneficiaryregistration

where br_beneficiarytypeid=6

)

select * into #temp1 from Duplicaterecords

where row>1

order by br_servicepointcode

select a.br_beneficiaryname,a.br_beneficiarysurname,a.br_fathername,

a.br_dob,a.br_districtid,a.br_mandalid,a.br_villageid,

masterdata.hihlmain.district.districtname, masterdata.hihlmain.Mandal.mandalname,

masterdata.hihlmain.village.villagename,

a.br_servicepointcode,sp_servicepointname,a.br_beneficiaryid,a.br_dateofregistration,a.br_visitdate

from TELEMONITORING.beneficiaryregistration a

inner join #temp1 b on a.br_servicepointcode=b.br_servicepointcode

inner join telemonitoring.servicepointregistration on sp_servicepointcode=a.br_servicepointcode

inner join masterdata.hihlmain.district on masterdata.hihlmain.district.Districtid=a.br_districtid

inner join masterdata.hihlmain.Mandal on masterdata.hihlmain.mandal.mandalid=a.br_mandalid

inner join masterdata.hihlmain.village on masterdata.hihlmain.village.villageid=a.br_villageid

and a.br_beneficiaryname=b.br_beneficiaryname

and a.br_beneficiarysurname=b.br_beneficiarysurname

and a.br_fathername=b.br_fathername

and a.br_districtid=b.br_districtid

and a.br_mandalid=b.br_mandalid

and a.br_villageid=b.br_villageid

and a.br_servicepointcode=b.br_servicepointcode

group by

a.br_districtid,a.br_mandalid,a.br_villageid,a.br_servicepointcode,

a.br_beneficiaryname,a.br_beneficiarysurname,a.br_fathername,a.br_dob,

a.br_beneficiaryid,a.br_dateofregistration,a.br_visitdate,sp_servicepointname,

masterdata.hihlmain.district.districtname, masterdata.hihlmain.Mandal.mandalname,

masterdata.hihlmain.village.villagename

drop table #temp1

Malleswarareddy
I.T.Analyst
MCITP(70-451)