Load only rows with min value

[#1]

Hi guys,

I'm trying to load "min values" from a table. I have redundant rows but I want only the row's which have the min Amount.

Example:

  ID, Amount

    1, 5

    1, 2

    2, 3

    2, 8

    3, 9

In this Example I want only back...

1,2

2,3

3,9

But I don't know to handle this.

Thank you

[ankit777]

Hi

You can use firstsortedvalue function

[Kushal_Chawda]

Data:

LOAD ID,

         min(Amount) as Amount

FROM table

Group by ID

[tamilarasu]

Hi Hans.

Try any one of the below methods,

Temp:

LOAD * INLINE [

    ID, Amount

    1, 5

    1, 2

    2, 3

    2, 8

    3, 9

];

NoConcatenate

LOAD ID,

FirstSortedValue(Amount, Amount)

Resident Temp Group by ID;

DROP Table Temp;

      OR

Temp:

LOAD * INLINE [

    ID, Amount

    1, 5

    1, 2

    2, 3

    2, 8

    3, 9

];

NoConcatenate

LOAD ID,

Min(Amount)

Resident Temp Group by ID;

DROP Table Temp;

[#5]

Okay and what about this...

ID, ID2,One, Two, Diff, Amount

    1, A, 10, 5, 5, 10

    1, A, 5, 2, 3, 25

    1, B, 6, 3, 3, 45

    1, B, 7, 3, 4, 45

    1, C, 8, 4, 4, 85

    2, A, 1, 0, 1, 50

I want to have following table:

ID, ID2, Amount

1, A, 25

1, B, 45

1, C, 85

2, A, 50

Based on min (Diff). How can I achive this?

[Kushal_Chawda]

LOAD ID,

          ID1,

          FirstSortedValue(Amount,Diff) as Amount

Group by ID,ID1;

[tamilarasu]

Fine.

Temp:

LOAD * INLINE [

   ID, ID2,One, Two, Diff, Amount

    1, A, 10, 5, 5, 10

    1, A, 5, 2, 3, 25

    1, B, 6, 3, 3, 45

    1, B, 7, 3, 4, 45

    1, C, 8, 4, 4, 85

    2, A, 1, 0, 1, 50

];

NoConcatenate

LOAD ID,

ID2,

Max(Amount)

Resident Temp Group by ID, ID2;

DROP Table Temp;

[Kushal_Chawda]

Hi Tamil,

what if the Data is? I think he is asking based on Min Diff

Temp:

LOAD * INLINE [

   ID, ID2,One, Two, Diff, Amount

    1, A, 10, 5, 5, 25

    1, A, 5, 2, 3, 10

    1, B, 6, 3, 3, 45

    1, B, 7, 3, 4, 45

    1, C, 8, 4, 4, 50

    2, A, 1, 0, 1, 80

];

[#9]

!Okay thank you for all the solutions but know you become my last example which im not able to finish. Look at teh excel sheet please. There you can see is a table on which every row is doubled because of the DateTwo join. Now I must choose the row where Diff is min. I have created a gap between the row we must compaire. I hope that anyone give me a solution about that problem.

Thank you !

[Kushal_Chawda]

How do you find the diff?