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 |
Regarding joins what are the differences you observed in oracle 9i and sql server?
what are rollup and cube in t-sql? : Transact sql
What is the usage of when clause in trigger?
What is schema in sql example?
HP Interview -2016 Unix 1) grep command in unix 2) what is set command
What is the difference between delete and truncate statement in sql?
what is 'mysqlshow'? : Sql dba
How to process query result in pl/sql?
What does bitemporal mean?
Explain unique key in sql.
Explain two easy sql optimizations.
Which sorts rows in sql?