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?

Answer 1

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;