Where exists alternative

[#1]

Hello Friends

I have a situation for which the code is below:

TempTableA:

Load max([Temp ID]) AS [Temp ID],[Orig Temp ID] from TableA.qvd(qvd) group by [Orig Temp ID];

TableA:

Load * from TableA.qvd(qvd) where exists ([Temp ID]) and exists ([Orig Temp ID]);

Drop Table TempTableA:

So basically, what I am trying to do with this piece of code is that for each [Orig Temp ID] I want the corresponding max([Temp ID]) only.

SalesPerson       Orig Temp ID           Temp ID

John Doe                  1                       111

John Doe                  1                      222

John Doe-New          1                       333

So therefore I want only the 3rd row to showup only in my output

So basically, what I am trying to do with this piece of code is that for each [Orig Temp ID] I want the corresponding max([Temp ID]) only.

Is using the wherexists a good option or is there an alternate best way to do?

Note: Temp ID is my primary Key

Thanks

[tamilarasu]

Hi Rizwan,

Try this,

Data:
LOAD SalesPerson,
[Orig Temp ID],
[Temp ID]
FROM
TableA.qvd
(qvd);

Inner Join
Load [Orig Temp ID],
Max( [Temp ID]) as [Temp ID]
Resident Data Group by [Orig Temp ID];

[sunny_talwar]

Another option:

TableA:

LOAD FirstSortedValue(SalesPerson, -[Temp ID])  as SalesPerson

          [Orig Temp ID],

          Max([Temp ID]) as [Temp ID]

FROM TableA.qvd (qvd)

Group By [Orig Temp ID];