SQL query:
You can use this kind of query to fetch more than 1 Max salary for each department.
SELECT * FROM (
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY, RANK () OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) SAL_RANK FROM EMPLOYEES)
WHERE SAL_RANK <= 2
Informatica Approach:
We can use the Rank transformation to achieve this.
Use Department_ID as the group key.
In the properties tab, select Top, 3.
The entire mapping should look like this.
This will give us the top 3 employees earning maximum salary in their respective departments.
