Find Duplicate Row

learnmf · Active User Joined: 14 Mar 2005 Posts: 123

Hi Friends

I have nother query.

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

Thanks
Chandra

notonly4u · New User Joined: 26 Apr 2005 Posts: 87 Location: Hyderabad

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

Please correct me If Iam wrong...

Regards
Tanden

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

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.

karthi_ind · Posted: Fri Sep 30, 2005 3:55 pm

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.

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

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

iknow · Posted: Thu Dec 15, 2005 12:22 pm

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:

priyesh.agrawal · Posted: Thu Dec 15, 2005 3:15 pm

iknow...

appasi · New User Joined: 12 Dec 2005 Posts: 20 Location: Bangalore

Hi iknow,

What Karthi_ind mentioned is right ,I am sure whether it works.
try it , Correct me if i m wrong.

iknow · Posted: Thu Dec 15, 2005 5:31 pm

Hi All,

What karthi_ind suggested is works fine.

Karthi_ind wrote:

anilbatta · New User Joined: 13 Nov 2005 Posts: 22

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;

gireeshsk · New User Joined: 20 Dec 2005 Posts: 6 Location: chennai

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

The Querry works fine

gireeshsk · New User Joined: 20 Dec 2005 Posts: 6 Location: chennai

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