Get distinct value

[sunilkumarqv]

Hi

Table :

    Inline

[

Date,Key

1/2/2015,A

1/2/2015,B

2/3/2015,A

2/3/2015,B

3/3/2015,c

] ;

Need to get o/p: Date,Key

                             3/3/2015,C

[sunny_talwar]

Is the idea to get the Date and Key where Date is maximum? or get Date and Key which are only showing up once in the database?

Best,

S

[PrashantSangle]

Hi,

USe max() or FirstSortedValue()

Regards

[JonnyPoole]

I agree there are multiple criteria that could result in 3/3/2015,C  returned . Please define what you need to return.  Also you used a capital 'C' and the data is lower case 'c' .  Please confirm that too.

[stabben23]

New_Table:

LOAD

Date,

Upper(Key) as Key

Resident Table;

Drop Table;

or?

[robert_mika · Accepted solution]

Table1 :

load  *   Inline

[

Date,Key

1/2/2015,A

1/2/2015,B

2/3/2015,A

2/3/2015,B

3/3/2015,c

] ;

join(Table1)

load

Key,

count(Key) as CountKey

Resident Table1

group by Key;

final:

load

Date,

Key

resident Table1

where CountKey=1;

drop table Table1;