To Find Max value in a One Column

[pra_kale]

Hi,

Below given is the sample data in which for one Policy there are Two or Three Premium values. I want a maximum value for that policy only through script so that will become my base data for further calculation.

Please suggest how I can achieve this in script

Policy_no	Premium	Name
12345678	5000	Jon
10002345	100000	Joseph
12345688	40000	Kern
12345278	60000	Ritu
12345678	3000	Jon
10002345	200000	Joseph
12345688	60000	Kern
12345278	40000	Ritu
12345688	100000	Kern
12345278	800000	Ritu
5678945	25000	Jeevan
369874	123980	Kiran

Final Out_put should be like this..

Policy_no	Premium	Name
12345678	5000	Jon
10002345	200000	Joseph
12345688	100000	Kern
12345278	800000	Ritu
5678945	25000	Jeevan
369874	123980	Kiran

Thanks in advance..

[#2]

select max(column_name)

[petter · Accepted solution]

Here is how you can achieve it in a load script:

B:

LOAD

  Policy_no,

  Max(Premium),

  FirstSortedValue(Name,Premium)

RESIDENT

  A

GROUP BY

  Policy_no;

[petter]

and if you don't need the A table at all for other purposes you could make this into a preceding load like this:

A:

LOAD

  Policy_no,

  Max(Premium),

  FirstSortedValue(Name,Premium)

GROUP BY

  Policy_no;

LOAD Policy_no,

     Premium,

     Name

FROM

  [sample_Max value.xlsx]

  (ooxml, embedded labels, table is Sheet1);

[petter]

or directly from the Excel file without preceding load:

A:

LOAD

  Policy_no,

  Max(Premium),

  FirstSortedValue(Name,Premium)

FROM

  [sample_Max value.xlsx]

  (ooxml, embedded labels, table is Sheet1)

GROUP BY

  Policy_no;

[pra_kale]

That's Great...Both the solutions are Helpful..

But, currently for further use I will go with Second solution..

Just One question..If I have more than 3 or 4 columns then I have to include in a script like this. Please correct me if I am wrong.. or any other way to this.

FirstSortedValue(Name,Premium,Column 3, Column 4, Column 5, Column 6,...etc).

[Kushal_Chawda]

In this case you can do like this

FirstSortedValue(Column 3,Premium) as Column 3,

FirstSortedValue(Column 4,Premium) as Column 4

FirstSortedValue(Column 5,Premium) as Column 5

and so on

[petter]

Adding a third parameter will only pick the n'th value so a number 2 for the third parameter will not select the first sorted value but the second sorted value. So you have to go for just using two parameters like: FirstSortedValue(Column N, Premium) AS ColN for each additional column.

[pra_kale]

Thanks for Your Help...That's Great.

[pra_kale]

Too Good..Great..Thanks for Your Help.