how to retrieve only duplicate values in a table

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

Question Posted / naren.bo

9 Answers
15021 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to retrieve only duplicate values in a table..

Answer / ravi patel

for the table emp
ID NAME
1 ravi
2 umang
1 ravi
3 nishant

now fire the query as beleow

select * from emp group by id,name having count(id)>1 and
count(name)>1;

Is This Answer Correct ?

3 Yes

0 No

how to retrieve only duplicate values in a table..

Answer / sri

For example,u have a table called student like below.

STUD-NAME SUBJECT
--------- ------
STUD1 A
STUD2 B
STUD2 A
STUD1 A

in this structure 1 row is duplicated in 4 th. so we can
fetch the student name using the below qry.

SELECT stud-name FROM STUDENT
GROUP BY stud-name,subject
HAVING COUNT > 1.

Is This Answer Correct ?

12 Yes

10 No

how to retrieve only duplicate values in a table..

Answer / p.rajasekar

select count(<Duplicate_FieldName>),fees
from Table Name
group by <Duplcate_FieldName>
having count(<Duplicate_FieldName>)>1

Regards
P.Rajasekar

Is This Answer Correct ?

2 Yes

1 No

how to retrieve only duplicate values in a table..

Answer / sixface

ID NAME
1 ravi
2 umang
1 ravi
3 nishant

SELECT id,name
FROM emp
WHERE id in
(
SELECT id
FROM emp
GROUP BY id
HAVING COUNT(*) > 1
);
It gives all duplicate rows........
Lets try.....

Is This Answer Correct ?

1 Yes

0 No

how to retrieve only duplicate values in a table..

Answer / suresh babu

select
id,
name
from
(select
id,
name
from
find_dup
group by
id,
name
having
count(*) > 1);

Is This Answer Correct ?

0 Yes

0 No

how to retrieve only duplicate values in a table..

Answer / siva

Select* from emp where rowid not in(select max(rowid) from emp group by empno);

Is This Answer Correct ?

0 Yes

0 No

how to retrieve only duplicate values in a table..

Answer / vijay_1994

select count (column_name),column_name from table_name group by column_name having count(column_name)>1;

Is This Answer Correct ?

0 Yes

0 No

how to retrieve only duplicate values in a table..

Answer / kirankumar.vangeti

For the same example given in the answer 1

select studentname, subject, count(studentname) as count
from student
group by studentname, subject
having (count(studentname)>1);

above query will give the results like

STUD1 A 2

Is This Answer Correct ?

0 Yes

1 No

how to retrieve only duplicate values in a table..

Answer / akki julak

FOR EXAMPLE WE HAD TAKE EMP TABLE AND ENAME,EMPNO AS COLUMNS

SELECT EMPNO,ENAME
FROM EMP
WHERE ROWID NOT IN(SELECT MAX(ROWID) FROM EMP
GROUP BY EMPNO,ENAME);

by
AKKI JULAKANTI

Is This Answer Correct ?

1 Yes

3 No

Post New Answer