Let Us Learn Oracle - Part 44 of N [ First and Last function of Oracle ]
Purpose: Both of these acts as analytical and aggregate function. They atc on a set of rows that act as First or Last as per the given sorting specification.
They accepts any numeric or non-numeric datatype that can be implicitly converted into a numeric datatype and returns a numeric value
Syntax: Aggregate_Function Keep (Dense_Rank First|Last Order By OrderBy_Clause) [Over PartitionBy Clause]
Where,
Aggregate_Function => Will operate only on those rows that rank either First or Last.
Keep => Indicates that only the First/Last value of Aggregate_Function will be returned
Dense_Rank First/Last => Will aggregate over only on those rows with the minimum (First) or maximum(Last) Dense Rank
Over Clause => It is optional. It's presence makes the functions as analytic and not aggregate.
Aggregate example
Suppose we want to find out for every department what is the lowest and highest salary paid. We can find as under
SQL> Select
2 DeptName
3 ,Min(Salary) Keep (Dense_Rank First Order By Salary) As "Lowest Salary"
4 ,Max(Salary) Keep (Dense_Rank Last Order By Salary) As "Highest Salary"
5 From tblemployee
6 Join tblDept
7 Using(DEPTID)
8 Group By DeptName;
DEPTNAME Lowest Salary Highest Salary
-------------------------------------------------- ------------- --------------
Accounts 1230 7900
Finance 520 7900
IT 5120 16000
Sales 650 650
Yes I agree that we can find the same easily by using Max and Min
SQL> Select
2 DeptName
3 ,Min(Salary) As "Lowest Salary"
4 ,Max(Salary) As "Highest Salary"
5 From tblemployee
6 Join tblDept
7 Using(DEPTID)
8 Group By DeptName;
DEPTNAME Lowest Salary Highest Salary
-------------------------------------------------- ------------- --------------
Accounts 1230 7900
IT 5120 16000
Sales 650 650
Finance 520 7900
Now suppose we need to find the highest and lowset salary for every department based on the Commission paid. For this we can do as under
SQL> Select
2 DName
3 ,Min(Sal) Keep (Dense_Rank First Order By Comm) As "Lowest Salary"
4 ,Max(Sal) Keep (Dense_Rank Last Order By Comm) As "Highest Salary"
5 From Emp
6 Join Dept
7 Using(DeptNo)
8 Group By DName;
DNAME Lowest Salary Highest Salary
-------------- ------------- --------------
ACCOUNTING 1300 5000
RESEARCH 800 3000
SALES 1500 2850
Analytic example
Suppoose we want to display the Salary of the employee as well as the highest and lowset salary for every department based on the Commission paid. For this we can do as under
SQL> Select
2 EmpNo,EName,DName,Sal
3 ,Min(Sal) Keep (Dense_Rank First Order By Comm) Over(Partition By DName) As "Deptwise Low Sal"
4 ,Max(Sal) Keep (Dense_Rank Last Order By Comm) Over(Partition By DName) As "Deptwise High Sal"
5 From Emp
6 Join Dept
7 Using(DeptNo);
EMPNO ENAME DNAME SAL Deptwise Low Sal Deptwise High Sal
---------- ---------- -------------- ---------- ---------------- -----------------
7782 CLARK ACCOUNTING 2450 1300 5000
7839 KING ACCOUNTING 5000 1300 5000
7934 MILLER ACCOUNTING 1300 1300 5000
7566 JONES RESEARCH 2975 800 3000
7902 FORD RESEARCH 3000 800 3000
7876 ADAMS RESEARCH 1100 800 3000
7369 SMITH RESEARCH 800 800 3000
7788 SCOTT RESEARCH 3000 800 3000
7521 WARD SALES 1250 1500 2850
7844 TURNER SALES 1500 1500 2850
7499 ALLEN SALES 1600 1500 2850
7900 JAMES SALES 950 1500 2850
7698 BLAKE SALES 2850 1500 2850
7654 MARTIN SALES 1250 1500 2850
14 rows selected.
So we can see at a glance the highest and lowest departmentwise salaries as well as the individual employe salaries
Thanks for reading