find highest earners in last 10 years-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

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;

CodePudding user response：

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;