IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Find Duplicate Row


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
learnmf

Active User


Joined: 14 Mar 2005
Posts: 123

PostPosted: Mon Jul 18, 2005 8:50 pm
Reply with quote

Hi Friends

I have nother query.

How to find the rows of a Table which contain only duplicate values.


Thanks
Chandra
Back to top
View user's profile Send private message
notonly4u

New User


Joined: 26 Apr 2005
Posts: 87
Location: Hyderabad

PostPosted: Mon Jul 18, 2005 8:55 pm
Reply with quote

Use Count(var-name) > 1 in the Where or Having clause.

Please correct me If Iam wrong...

Regards
Tanden
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Tue Jul 19, 2005 2:00 am
Reply with quote

Hi,



SELECT * FROM STUDENT GROUP BY STUDENTID , STUDENTNAME HAVING COUNT(*)
> 1 ;

Here are the duplicate rows in a relation student :
studentid | studentname
-----------+-------------
102 | mital
103 | dmitk

Hope it helped.
Back to top
View user's profile Send private message
karthi_ind

Active User


Joined: 24 Feb 2004
Posts: 131
Location: Chennai

PostPosted: Fri Sep 30, 2005 3:55 pm
Reply with quote

Hi Parishit

The query u hve mentioned will give error a
Group is inconsistent.

If u give any coln after Group by , u shld mention thse cols in the
select statement so the query shld be

SELECT STUDENTID , STUDENTNAME FROM STUDENT GROUP BY STUDENTID , STUDENTNAME HAVING COUNT(*) > 1 ;

Correct me if i m wrong.

Karthi G.
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Thu Dec 15, 2005 11:39 am
Reply with quote

Hi,

My assumption was to have only two columns in the table that I have used.

If all the columns in the table are used in the group by clause, then I think we can use * in the query as mentioned above
Back to top
View user's profile Send private message
iknow

Active User


Joined: 22 Aug 2005
Posts: 411
Location: Colarado, US

PostPosted: Thu Dec 15, 2005 12:22 pm
Reply with quote

Hi learnmf,


Here is an easy to follow technique.

Assuming a table TABLE_X with column_1, column_2, and column_3, and assuming the aim is to find all columns who happen to have exact duplicate combination of data for these three columns: You would key, in the SQL command line the following command:

Code:
SELECT COLUMN_1, COLUMN_2, COLUMN_3, COUNT(*)
FROM TABLE_X
HAVING COUNT(*) > 1
GROUP BY COLUMN_1, COLUMN_2, COLUMN_3


NOTE

What Karthi_ind mentioned is right but in his query he has interchanged GROUP BY and HAVING. I am not sure whether it works.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Thu Dec 15, 2005 3:15 pm
Reply with quote

iknow...

Quote:
SELECT COLUMN_1, COLUMN_2, COLUMN_3, COUNT(*)
FROM TABLE_X
HAVING COUNT(*) > 1
GROUP BY COLUMN_1, COLUMN_2, COLUMN_3

Was that tested before posting as a reply to the question. Try to run the query you posted.

HAVING Clause is not used like that....
Quote:
NOTE

What Karthi_ind mentioned is right but in his query he has interchanged GROUP BY and HAVING. I am not sure whether it works.

What Karthi_ind mentioned is correct.... HAVING is used with GROUP BY clause only....

Regards,

Priyesh.
Back to top
View user's profile Send private message
appasi

New User


Joined: 12 Dec 2005
Posts: 20
Location: Bangalore

PostPosted: Thu Dec 15, 2005 3:27 pm
Reply with quote

Hi iknow,

What Karthi_ind mentioned is right ,I am sure whether it works.
try it , Correct me if i m wrong.
Back to top
View user's profile Send private message
iknow

Active User


Joined: 22 Aug 2005
Posts: 411
Location: Colarado, US

PostPosted: Thu Dec 15, 2005 5:31 pm
Reply with quote

Hi All,

What karthi_ind suggested is works fine.

Karthi_ind wrote:

Quote:
SELECT STUDENTID , STUDENTNAME FROM STUDENT GROUP BY STUDENTID , STUDENTNAME HAVING COUNT(*) > 1 ;



Mine is showing error in HAVING CLAUSE.

Sorry for the inconvenience.
Back to top
View user's profile Send private message
anilbatta

New User


Joined: 13 Nov 2005
Posts: 22

PostPosted: Fri Dec 16, 2005 3:22 pm
Reply with quote

Hi

Inorder to find the rows of a table which are having the duplicate values, the following is the query in which table is having with columns as ID, NAME in a table called STUDENT.

SELECT ID, NAME FROM STUDENT HAVING COUNT(*)>1
GROUP BY ID, NAME;
Back to top
View user's profile Send private message
gireeshsk

New User


Joined: 20 Dec 2005
Posts: 6
Location: chennai

PostPosted: Tue Dec 20, 2005 8:00 pm
Reply with quote

SELECT STUDENTID , STUDENTNAME FROM STUDENT GROUP BY STUDENTID , STUDENTNAME HAVING COUNT(*) > 1 ;


The Querry works fine
Back to top
View user's profile Send private message
gireeshsk

New User


Joined: 20 Dec 2005
Posts: 6
Location: chennai

PostPosted: Tue Dec 20, 2005 8:12 pm
Reply with quote

hi fiends

how can i delete a duplicate records from the table retaining the original row(atleast one row of the duplicates)

should i have to use rowid
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts sort to find out the char which repea... Mainframe Interview Questions 10
No new posts Compare 2 alphabets in a REXX and fin... CLIST & REXX 2
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Find the size of a PS file before rea... COBOL Programming 13
Search our Forums:

Back to Top