Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Find Duplicate Row

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Find Duplicate Row
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    Post subject:
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    Post subject: Re: Find Duplicate Row
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    Post subject: Re: Find Duplicate Row
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    Post subject:
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: 413
Location: Colarado, US

PostPosted: Thu Dec 15, 2005 12:22 pm    Post subject: Re: Find Duplicate Row
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: 1452
Location: Chicago, IL

PostPosted: Thu Dec 15, 2005 3:15 pm    Post subject: Re: Find Duplicate Row
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    Post subject:
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: 413
Location: Colarado, US

PostPosted: Thu Dec 15, 2005 5:31 pm    Post subject: Re: Find Duplicate Row
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    Post subject:
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    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts To find out size allocated to a seque... ashek15 JCL & VSAM 15 Thu Apr 27, 2017 9:42 am
No new posts Sort Card to Remove Duplicate records... raj4neo SYNCSORT 2 Wed Jan 25, 2017 4:44 am
No new posts find particular member name in PDS us... ravi243 CLIST & REXX 10 Mon Dec 19, 2016 6:44 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us