sql query

[mgl70]

Hi,
how to select the duplicate records(entire row is not duplicate, only couple of columns only duplicated) from a table.
for ex: emp
--------
empno ename hiredate(varchar2) sal
1 first 01-01-1980 5000
2 first 01-01-1980 4000
3 first 01-01-1980 3000
4 second 01-01-1960 6000
5 third 01-01-1970 7000


How to find out the records where ename should match with hiredate more than one record.

The query should result:

empno ename hiredate(varchar2) sal
1 first 01-01-1980 5000
2 first 01-01-1980 4000
3 first 01-01-1980 3000


How to write the query for the above result.

(I know the query if the entire row is duplicate.
select * from emp
group by empno,ename,hiredate,sal
having count(ename || hiredate) > 1;
).

I tried with maxrowid also. but could not solve.

help please.

Thanks very much.

 

[SantaMufasa]

MGL,

Try this:

select * from emp
where (ename,hiredate)
   in (select ename,hiredate from emp
        group by ename,hiredate
       having count(*) > 1);

EMPNO ENAME      HIREDATE          SAL
----- ---------- ---------- ----------
    1 first      01-01-1980       5000
    2 first      01-01-1980       4000
    3 first      01-01-1980       3000

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[mgl70]

Yes, YOUR query is working good for my emp test table. but,It is giving wrong data with original table(AT MY WORK PLACE).
I donot know why.

But I did it with selfjoin. then it worked.

select A.ename,A.hiredate,A.empno,A.salary
from emp A, emp B
where A.ename = B.ename
and A.hiredate = B.hiredate
and A.EMPNO != B.EMPNO;


I donot know why it did not work with your query at my work .

Any way, Thanks for your help Mufasa. you helped me a lot.

Thanks once again.
regards.

 

[SantaMufasa]

MGL,

Nope...your code gives incorrect results...double the rows that should appear, because

when A.EMPNO = 1, you will get rows B.EMPNO = 2, 3
when A.EMPNO = 2, you will get rows B.EMPNO = 1, 3
when A.EMPNO = 3, you will get rows B.EMPNO = 1, 2

...thus you get six rows...double what you want. You could "fudge" the results with a DISTINCT, but that's fudging.

Perhaps the reason my code didn't work is because in your original specs, the salary column was spelled "SAL", whereas in your more recent "working" code, you spelled it "SALARY". Regardless, once the columns are consistent, there is no reason that the original suggestion should not work.

Let us know.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[mgl70]

yes, I forgot to type distinct when I was replying to you. but in my original query I did it. I got it right. work is done.

The query which you gave yesterday is working fine for my test table(emp) which I created at home(5 records).

but, at work it is giving bad data with the same query.
My work table has 40,000 rows.

At my work, data is like this:
Employee Full Name : xxx\yyy\zzz\ varchar2
dob : 19800101 varchar2

I am wondering why it is giving wrong data. It not supposed to.

regarding sal, salary are my typing mistakes. it does not matter.

ok. I will try again this eve or tomorrow.

Thanks.

 

[mgl70]

The weird thing is with my original query in work place, I need to add couple of other clauses also. I tried with different combinations putting clauses in inner and outer queries. One of the trial was worked .

Thanks . Now, your query is working fine with original table.

Still, I did not get why my other trails did not work(I feel the clauses are in right place.) I donot know.
Any way, it is working fine. Thanks very much.
bye.