Tuesday, December 18, 2012

Oracle Tip : Using RANK in Oracle


Scenario:-
----------
Suppose we have a sample table for employee salary details

Table :- EmpSalary

UserID EmpName         SalaryDate              Salary
--------  --------------           --------------            ---------
1            XYZ 01/02/2012       1000
2            ABC 01/01/2012       1500
1            XYZ 01/03/2012       3000
2            ABC 01/02/2012       5000
1            XYZ 01/04/2012       4500
2           ABC 01/03/2012       7000

I want to get the initial salary of employees.

Solution:-
----------
You can use Rank analytic function of Oracle. 

SYNTAX:- RANK() OVER (PARTITION BY fieldname1 ORDER BY fieldname2)

PARTITION BY is Similar to Group by . The RANK function will group by fieldname1 and then Order by fieldname2 and Rank the records from 1 , It also resets when userid changes.


select * from (Select
    UserId,
    EmpName,
    SalaryDate,
    Salary,
    Rank() over (PARTITION BY UserId ORDER BY SalaryDate) Ranks
from
    EmpSalary) where Ranks=1

The Result of the above query is as follows


UserID EmpName         SalaryDate              Salary
--------  --------------           --------------            ---------
1            XYZ 01/02/2012       1000
2            ABC 01/01/2012       1500


There are more Analytic Functions which you can be very useful. You can Check Out Other Analytic Functions Here
Digg Google Bookmarks reddit Mixx StumbleUpon Technorati Yahoo! Buzz DesignFloat Delicious BlinkList Furl

0 comments : on " Oracle Tip : Using RANK in Oracle "