How to get distinct values

[Anonymous]

Hi,

I have two columns with repeated values:

  F1, F2

    1, a

    2, f

    2, aaaa

    3, b

    4, bb

    5, c

    5, c

    5, c

and how can i get the result shown below:

  F1, F2

    1, a

    2, f

    3, b

    4, bb

    5, c

[avinashelite]

try the

FirstSortedValue()

[masismykola]

Hi,¨

after Load statement write Distinct. This will get you only unique records.

Please mark as answered.

Thank you.

[Anonymous]

Hi Avinash,

I tried your solution, but am missing something.

Load * inline [F1, F2

    1, a

    2, f

    2, aaaa

    3, b

    4, bb

    5, c

    5, c

    5, c];

    load2:

    NoConcatenate

load F1,

firstsortedvalue(F2,F1) as f2 Resident Load

group by F1;

Drop table Load;

It doesnt work. Can you please tell me where i am doing it wrong??

[aarkay29]

Try this:

Table:

LOAD * INLINE [

F1, F2

    1, a

    2, f

    2, aaaa

    3, b

    4, bb

    5, c

    5, c

    5, c

];

Load 

  F1,

  FirstValue(F2)

Resident

  Table

Group by

  F1;

Drop Table

  Table;

[maxgro]

source:

LOAD *, rowno() as r INLINE [

  F1, F2

    1, a

    2, f

    2, aaaa

    3, b

    4, bb

    5, c

    5, c

    5, c

    ];

final:

NOCONCATENATE LOAD

  F1, F2

RESIDENT source

WHERE F1 <> Peek('F1')

ORDER BY r

;

DROP TABLE source;

[MarcoWedel · Accepted solution]

Hi,

another solution could be:

LOAD * INLINE [
    F1, F2
    1, a
    2, f
    2, aaaa
    3, b
    4, bb
    5, c
    5, c
    5, c
] Where not Exists (F1);

hope this helps

regards

Marco

[Anonymous]

Your solution worked for me!!!!

[Anonymous]

Thank you Marco.

worked!!