Where Exists() not filtering records

[FleetingImage]

I have a  qvd table  that looks like this:

Id	key	Amt
670966	1	624.19
670966	2	620
670966	3	645.13

*It has about 6M records

I want to keep the record with the max(key), so I'm doing as follows:

tbl1:

Load

Id

,key

,Amt

from tbl1.qvd

noconcatenate

max_key:

Load

Id

,max(key) as maxKey

Resident tbl1;

noconcatenate

tblNoDup:

Load

*

Resident tbl1

where Exists(maxKey, key)

;

drop table tbl1,  max_key;

I expect that tblNoDup shows:

Id	key	Amt
670966	3	645.13

But Instead, is returning all three records.  What am I missing?

[dplr-rn · Accepted solution]

add a group by to your max load

max_key:

Load

Id

,max(key) as maxKey

Resident tbl1 group by Id;