Where Exists() not filtering records

FleetingImage — Sat, 16 Nov 2024 18:19:00 GMT

I have a qvd table that looks like this:

*It has about 6M records

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

tbl1:

Load

,key

,Amt

from tbl1.qvd

noconcatenate

max_key:

Load

,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?

Re: Where Exists() not filtering records

dplr-rn — Fri, 24 Jul 2020 17:22:36 GMT

add a group by to your max load

max_key:

Load

,max(key) as maxKey

Resident tbl1 group by Id;