Register now or log in to join your professional community.
I completed my graduation last year and i am getting started with SQL. can anyone help me to solve this query.
Table name is Employee and it has the following fields EMPNO, ENAME, SAL, JOB, DEPTNO. I want to find only the duplicate data's from the table. I tried using aggregate functions i couldn't get the required result.
Expecting EMPNO is essential/primary key and ENAME has duplicate values then you can use following SQL.
select ENAME from EMPLOYEE group by ENAME having count(ENAME) > 1;
SELECT column_name
FROM tablename
GROUP BY column_name
HAVING COUNT(column_name ) > 1
select column1, column2..., count(columnlast)
from table_name
where (all_conditions_if_required)
group by columnlast ------ (if it is the comlumn with duplicate data)
having count(columnlast) > 1 ;
In order to find the duplicate data from the table you can use the below query.
select empno,ename,sal,job,dept from employee
group by empno,ename,sal,job,dept from employee having count(1) > 1.
We have other methods also using analytical function, but this is easiest u can use.
You can use HAVING keyword along with GROUP BY. For example,
SELECT *
FROM tablename
GROUP BY fieldname1
HAVING COUNT(fieldname1) > 1
fieldname1 is the name of the column that contain duplicate values.
select distinct * from employee;
select * from Employee group by empno,ename, sal, job, deptno having count(*)>1;
select * from employee where rowid not in (select min(rowid) from emloyee group by empno);
SELECT ROWID,empno,ename,sal,job,deptno
FROM employee mq
WHERE mq.ROWID > ANY( SELECT csq.ROWID
FROM employee csq
WHERE csq.empno = mq.empno
AND csq.ename = mq.ename
AND csq.sal = mq.sal
AND csq.job = mq.job
AND csq.deptno = mq.deptno );
select EMPNO, ENAME, SAL, JOB, DEPTNO from employee group by EMPNO, ENAME, SAL, JOB, DEPTNO having count(*)>=2;
