How to achieve rank functionality in Datastage

[prasad v]

Hi

I need to implement a job to get the Top 2 nd salary in Datastage.
Datasource is MS Sql Server.
Datastage Version is 7.5 Parallel.

I know we can write a query and keep it in the jobs can achieve.
but i need to build this in Job as we need to improve the performance of the Jobs.

You can take an example data of emp table in oracle database.

Can any one advise how to do this?

[ray.wurlod]

This is a query, or report. It is not the type of task an ETL should be asked to perform.

There IS a solution, using stage variables. Even without sorting, remember the highest and second highest value thus far processed and, downstream of the Transformer stage, limit output to the last row containing the value(s) of the stage variable(s) as link column(s).

[keshav0307]

i need to build this in Job as we need to improve the performance of the Jobs

Not Sure if you get it.


do you want top 2 salaried employee of all department, or top 2 salaried employee in each department??

[prasad v]

I need top 2 salaries in the entire EMP table irrespective of Dept.

[ray.wurlod]

Select sorted in descending order. Constrain on input row number = 2. Read what I said about this not being an ETL task.