Filter Out Lowest Record - Advice Needed

[haymarketpaul]

I have data in a table like this.

Within the load script I need to filter out or flag the lowest UserID where their GMC#s match.  I need to keep the records that have no UserID as well.

Rec#	UserID	CompID	GMC#
1	123	456	123456
2	789	456	123456
3		987	234567
4		1234	345678
5	321		987654
6	987		987654

So from the above data i would end up with Rec#s   2,3,4,6    with Rec# 1 & 5 either filtered out or flagged

I was trying to use an approach like the one below but i end up losing the records with no UserID

Inner Join (tbl1)

LOAD

    GMC#     

    ,Max(UserID)        as UserID

Resident tbl1

Group By GMC#;

Any Ideas how best to approach this much appreciated

[swuehl · Accepted solution]

Try like this to flag your records:

T1:

LOAD * FROM YourTable;

LEFT JOIN

LOAD

     min(UserID) as UserID,

     [GMC#],

     1 as Flag

RESIDENT T1

GROUP BY [GMC#];

[haymarketpaul]

Thanks for reply - that works although i've now realised my data is more complex than i thought