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 |
What is the use of function in sql?
What is difference between inner join and self join?
What is the difference between rollback and rollback to statements?
What are analytical functions in sql?
Does sql between include endpoints?
what is HASH join?
What are the different types of functions in sql?
Can we create view in stored procedure?
I have a table with 1 million records out of which 10,000 records are of unique records, then if I will implement index, then which type of index shall I use and why shall I use?
Explain the difference in execution of triggers and stored procedures?
what are date and time data types? : Sql dba
What plvcmt and plvrb does in pl/sql?