DSXchange

the source table sal
empno deptno sal
1 10 10,000
2 20 20,000
3 30 30,000
1 40 15,000
2 50 22,000
3 60 25,000
. . .
. . .
. . .
. . .
. . .
. . .
find the which employ having max(sal) in which deptno(output should be display all columns)


target like this
----------
empno deptno sal
1 40 15,000
2 50 22,000
3 60 25,000
. . .
. . .
. . .
. . .
. . .

You can use an aggregator to group by on empno, deptno and get the max(sal). You can use a Remove Duplicates Stage with empno, deptno as the keys and you can sort the data on sal and retain the value you want.

... or just an inner join on empno and sal once the max has been determined in the Aggregator stage.