Want to find top 3 paid manager in each department in definite year
I want to know every information(all the field) of the top 3 paid managers of every department of a definite year for example 2002. I might need to use window function and convert to 'from_date' to 'to_date' to year. can i get the code?
Hi Abrar!
Thanks for reaching out.
We are currently producing lectures on window functions, common table expressions and temporary tables that we will hopefully post in only a few weeks time. You will be notified when we are ready. We suggest you visit the lectures and do the relevant exercises when posted.
In the meantime, there are two types of syntax to abide by. But in any of the two, I suppose you can use all managers ordered by their salary in your window specification and provide the rest of the conditions in a WHERE clause of the query.
SELECT a.emp_no,
a.salary AS max_salary FROM (
SELECT
emp_no, salary, ROW_NUMBER() OVER w as row_num
FROM
salaries
WINDOW w AS (PARTITION BY emp_no ORDER BY salary DESC)) a
WHERE a.row_num IN (1, 2, 3);
Hope this helps.
Kind regards,
Martin