On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Friday, June 13, 2008

Show Information on Duplicate Values



This report will let you not only see which rows have duplicate values, but the data associated with those values. In this example, I'm looking for the information for people with the same job.

The Break statement lets us view the duplicates in groups. I'm selecting the fields I want displayed, and then asking for a count within a job value. The parent query weeds out all the jobs that have only one value (i.e., King, President)
Break on Job Dup Skip 1
Select Job, EmpNo, EName, Hiredate, Sal
From
(
Select Job,
EmpNo,
EName,
Hiredate,
Sal,
------------------------------------------
Count(EName) Over (Partition By Job)
As
Cnt_Nm
------------------------------------------
From Emp
)
Where Cnt_Nm > 1;


See Oracle documentation for further detail:
Partition By clause

No comments:

Post a Comment