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.

Saturday, May 31, 2008

Row_Number() Over Partition By



This function can come in very handy. As shown in this example, Row_Number numbers each row within a department in order of the hiredate.
Select     Row_Number()
Over
(Partition By Deptno
Order By Hiredate)
As
Ord,
-------------------------------
EmpNo,
Ename,
Job,
HireDate,
Deptno
From Emp;


This then allows you to generate a report of the first person to be hired by each department.
Select *
From
(
Select Row_Number()
Over
(Partition By Deptno
Order By Hiredate)
As
Ord,
-------------------------------
EmpNo,
Ename,
Job,
HireDate,
Deptno
From Emp
)
Where Ord = 1
/
See Oracle documentation for further detail:
Partition By clause

No comments:

Post a Comment