find highest earners in last 10 years

Question

create table empSal
(
  pername varchar(200),
  salary number(10,2),
  salDate date
);

begin
insert into empsal values('ak',6300.0,'31-JAN-2012');
insert into empsal values('js',6400.0,'31-JAN-2012');
insert into empsal values('pk',6500.0,'31-JAN-2012');
insert into empsal values('ak',6000.0,'28-FEB-2012');
insert into empsal values('js',5800.0,'28-FEB-2012');
insert into empsal values('pk',2300.0,'28-FEB-2012');
end;

given empsal table(defintion given) and each person is getting salary at the end of the month for many years. need to write plsql query to find 3 highest earners on last 10 years of data?

Does this answer your question? [Oracle SELECT TOP 10 records](https://stackoverflow.com/questions/2498035/oracle-select-top-10-records) — astentx, Sep 15 '21 at 08:10

score 2 · Accepted Answer · answered Sep 15 '21 at 07:36

2

Shame you didn't actually run code you posted (as it would fail; table can't have that primary key, it doesn't make sense).

Anyway: sum salaries in last 10 years and rank them in descending order. Then select desired ones.

WITH
   temp
   AS
      (  SELECT pername,
                SUM (salary) sumsal,
                RANK () OVER (ORDER BY SUM (salary) DESC) rnk
           FROM empsal
          WHERE EXTRACT (YEAR FROM saldate) >= EXTRACT (YEAR FROM SYSDATE) - 10
       GROUP BY pername)
SELECT pername, sumsal
  FROM temp
 WHERE rnk <= 3;

answered Sep 15 '21 at 07:36

Littlefoot

131,892
15
35
57

sorry that was mistake in table creation. I was just considering dummy data – akki2346 Sep 15 '21 at 07:48
`EXTRACT (YEAR FROM saldate)` should be `saldate >= trunc(sysdate, 'yyyy') - interval '10' year` to take advantage of partitions/indexes – astentx Sep 15 '21 at 08:04

score 1 · Answer 2 · answered Sep 15 '21 at 07:35

We can use the RANK analytic function here:

WITH cte AS (
    SELECT pername, SUM(salary) AS total_salary,
           RANK() OVER (ORDER BY SUM(salary) DESC) sal_rank
    FROM empSal
    WHERE salDate >= TRUNC(sysdate, 'YEAR') - INTERVAL '10 years'
    GROUP BY pername
)

SELECT pername, total_salary
FROM cte
WHERE sal_rank <= 3
ORDER BY sal_rank DESC;

score 0 · Answer 3 · edited Sep 15 '21 at 08:15

The primary key in EMPSAL table will not allow you to insert the same employee more than once as you intend to do. Kindly remove it or create another column as your primary key.

Below is a basic select statement that would pick the top three earner in the last 10 years from the current date.

with salary_tenyears as (
    select pername , sum(salary ) salary 
    from empsal 
    where salDate >= trunc(add_months(sysdate,-12*10),'MM')
    group by pername
)
select *
from ( select pername,salary  , row_number() over( order by salary desc) rnk
        from salary_tenyears
) a
where rnk <=3;

find highest earners in last 10 years

3 Answers3