CODEWITHSUNDEEP
sqloracle-database
max samuel
max samuel

Reputation: 1

How to use rank to get the latest bonus record

currently I am using the below query to get the previous year’s bonus amount for the employees. But I am facing some issues, so I am trying to get the latest element entry value(screen entry value) for the element ‘xyz bonus’ using the RANK() function. Please help. Thanks.

Select 
Pam.assignment_number,
Peev.screen_entry_value as bonus_amount
From
Per_all_assignments_m Pam,
Pay_element_entries_f peef,
Pay_element_types_tl petl,
Pay_element_entry_values_f peev
Where
Pam.Person_id=peef.person_id
and peef.element_type_id = petl. element_type_id
And peef.element_entry_id = peev. element_entry_id
And petl.language=‘US’
And to_char(peef.effective_start_date,’yyyy’)=(to_char(sysdate,’yyyy’)-1)
And to_char(peev.effective_start_date,’yyyy’)=(to_char(sysdate,’yyyy’)-1)
And petl.element_name = ‘xyz bonus’

Upvotes: 0

Views: 134

Answers (1)

Littlefoot
Littlefoot

Reputation: 142968

As I don't have your tables, I'm using Scott's sample EMP table.

In there, rows sorted by salaries per department look like this:

SQL> select deptno,
  2         ename,
  3         sal,
  4         rank() over (partition by deptno order by sal desc) rn
  5  from emp
  6  order by deptno,
  7           sal desc;

    DEPTNO ENAME             SAL         RN
---------- ---------- ---------- ----------
        10 KING            10000          1
        10 CLARK            2450          2
        10 MILLER           1300          3
        20 SCOTT            3000          1
        20 FORD             3000          1
        20 JONES            2975          3
        20 ADAMS            1100          4
        20 SMITH             920          5
        30 BLAKE            2850          1
        30 ALLEN            1600          2
        30 TURNER           1500          3
        30 MARTIN           1250          4
        30 WARD             1250          4
        30 JAMES             950          6

14 rows selected.

SQL>

If you want to fetch the highest salary per department, you'd then

SQL> select deptno, ename, sal
  2  from (select deptno,
  3               ename,
  4               sal,
  5               rank() over (partition by deptno order by sal desc) rn
  6        from emp
  7       )
  8  where rn = 1;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING            10000
        20 SCOTT            3000
        20 FORD             3000
        30 BLAKE            2850

SQL>

I guess that's what you are looking for.

Your query might then look like this:

Select 
  Pam.assignment_number,
  Peev.screen_entry_value as bonus_amount,
  rank() over (partition by pam.assignment_number order by peev.screen_entry_value desc) rn
From
  ...

Now, use it as an inline view (or a CTE) and fetch desired values.

If that's not what you are looking for, please, post sample data and desired result.

Upvotes: 1

Related Questions