how to retrieve only duplicate values in a table
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
How do I remove sql developer from windows 10?
what are the limitations of mysql in comparison of oracle? Mysql vs. Oracle. : Sql dba
What are the types of join and explain each?
Can we use insert statement in function?
what is the difference between stored procedure and packaged procedure
How do I get sql certification?
Is primary key a clustered index?
What are the disadvantages of file system?
Can we insert data in view?
Is delete faster than truncate?
How to convert lowercase letters to uppercase and uppercase letters to lowercase in a string. (ex, AbcdEfG should convert as aBCDeFg)
What is pl sql block in dbms?