6 Replies Latest reply: Nov 15, 2017 11:18 PM by Monica Lingan

# Largest unique value

Hi,

I am trying to pull the largest value from one column based on a unique value in a different column.

1. Column A contains order numbers (need highest number)

2. Column B contains a serial number that I need a unique value from.

AB
1222

2

222
3333
4111
5333
6222

I want the output to be something like this:

AB
4111

6

222
5333
• ###### Re: Largest unique value

May be this

LOAD B,

Max(A) as A

FROM ....

Group By B;

• ###### Re: Largest unique value

May be you need the order also then try this.

Src:

LOAD * Inline [

A,    B

1,    222

2,    222

3,    333

4,    111

5,    333

6,    222 ];

NoConcatenate

New:

LOAD

B,Max(A) as A

Resident Src

Group By B Order By B asc;

DROP Table Src;

• ###### Re: Largest unique value

This seems to have worked as expected

The issue now, is that the A column should only retrieve data based on the matches it find in a different table.

A.Alpha should validate it's data from A.Bravo.

Thanks in advance

• ###### Re: Largest unique value

Hi Johan,

Use below code,

Inline:

LOAD * INLINE [

A, B

1, 222

2, 222

3, 333

4, 111

5, 333

6, 222

];

Inner join(Inline)

Load B,A

Resident Inline

where if(B=peek(B),peek("Row Number")+1,1)=1

Order by B asc,A desc

Hope it helps

Thanks,

Monica

• ###### Re: Largest unique value

Hi Moica, thank you for the reply.

I seem to have forgotten to mention that the data is on a MSSQL server.

Guessing that is why I got the following error:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'peek' is not a recognized built-in function name.

• ###### Re: Largest unique value

Largestuniquevalues:

LOAD A,

B;

SQL SELECT A,

B

FROM MLINGAN.dbo."tbl_largestuniquevalues";

Inner join(Largestuniquevalues)

Load B,A

Resident Largestuniquevalues

where if(B=peek(B),peek("Row Number")+1,1)=1

Order by B asc,A desc;