simple distinct problem

[#1]

Should really be able to do this by now !!!

The file could contain multiple product codes for a ean-code, I only want one ean-code in the end table. I have tried the following but my second table keeps coming back empty, am I totally missing something or am I going about this in the wrong way ?

Ta,

Gav.

Gav:
LOAD "ean-code",
 "p-code";
SQL SELECT "ean-code",
 "p-code"
FROM PUB.eancode;

Gavdist:
load [ean-code],
 [p-code],
 recno() as int
 resident Gav where not exists([ean-code]);
drop field int;
drop table Gav;

[Oleg_Troyansky]

your code is chopped, really hard to understand what was there...

The answer to loading a list of distinct values is using a keyword "distinct" :

load distinct

ean_code

from ...

However, if you are loading more than one field, "distinct" applies to all values. So, if you have 2 Items for the ame EAN code (I won't tell you to UCC :-)), you need to use grouping and some kind of selective criteria to pick one item over the others. As a simplistic example, you can always pick the highest value. For example:

load distinct

ean_code,

MaxString (item_number) as Item

from...

group by ean_code

cheers,

Oleg

[#3]

Thanks for the reply,

Not sure what happened to the code, have fixed now.

Can I use group by on a resident table ? I can't seem to get it to work.

[Anonymous]

I'm not really sure what you're trying to do here. However, when doing the resident load you have a where condition that looks at rows where not exists ean-code. All values exist already since you're doing a resident load so the values can be found in the field ean-code. Therefore no rows will be loaded.

[#5]

Thanks for replying,

That makes since, the situation is that I am reading a table in via SQL in a pre-determined order. I am then trying to only get the first (or last , I can change the SQL order) instance of ean-code and the corresponding p-code, I can't use distinct because the p-code will be different. I have tried a combination of lastvalue() and group by but keep coming up with a Invalid expression error.

Ta,

[#6 · Accepted solution]

Thanks for the help, the group by worked fine, I have it working now. Thanks again.