Largest unique value

[johanru1]

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.

A	B
1	222
2	222
3	333
4	111
5	333
6	222

I want the output to be something like this:

A	B
4	111
6	222
5	333

[sunny_talwar]

May be this

LOAD B,

     Max(A) as A

FROM ....

Group By B;

[its_anandrjs · Accepted solution]

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;

[Monicalingan]

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

[johanru1]

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.

[johanru1]

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

[Monicalingan]

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;