How to get top 3 salaried for each Department from Employee Table.

Approach 1 using Rank Transformation

Create the mapping as below:

Use sorter transformation to sort record order by Deptno and Sal in Ascending.

In Ranker Transformation Select Group by for Deptno, Rank value for Sal in ports tab

  and Properties tab of Ranker Transformation Select Top 3 Ranks as below 
Map required transformation the ports from Rank Transformation to Target Instance

Approach 2 using without Ranker Transformation

In Sorter Transformation select Deptno, Sal ports

 

In Expression Transformation compare current process row deptno with previous processed row, which will assign sequence number to input records for each deptno.

 
expression logic:

var_seq_dept_wise_sal = IIF(DEPTNO=var_pre_deptno, var_seq_dept_wise_sal+1, 1)

In Filter Transformation use condition to pass only 3 records from each deptno based on flag created in expression.

Map required ports from Filter Transformation to Target Instance.