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 |
find the third highest salary?
what is 'trigger' in sql? : Sql dba
how to see the oracle 9i/10g table in pc? or In my pc where the table is saved and how to see?
What is the least restrictive isolation level? : Transact sql
What is the main reason behind using an index?
What does an inner join do?
Mention what is the plv (pl/vision) package offers?
What are the disadvantages of file system?
what is an index? : Sql dba
What are the types of operators available in sql?
consider a table which contain 4 columns,ename,eno,sal and deptno, from this table i want to know ename who having maximum salary in deptno 10 and 20.
What is sqlca in db2?