20 Replies Latest reply: May 20, 2016 8:54 AM by Tamil Nagaraj

# Load only rows with min value

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

• ###### Re: Load only rows with min value

Hi

You can use firstsortedvalue function

• ###### Re: Load only rows with min value

Data:

min(Amount) as Amount

FROM table

Group by ID

• ###### Re: Load only rows with min value

Hi Hans.

Try any one of the below methods,

Temp:

ID, Amount

1, 5

1, 2

2, 3

2, 8

3, 9

];

NoConcatenate

FirstSortedValue(Amount, Amount)

Resident Temp Group by ID;

DROP Table Temp;

OR

Temp:

ID, Amount

1, 5

1, 2

2, 3

2, 8

3, 9

];

NoConcatenate

Min(Amount)

Resident Temp Group by ID;

DROP Table Temp;

• ###### Re: Load only rows with min value

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?

• ###### Re: Load only rows with min value

ID1,

FirstSortedValue(Amount,Diff) as Amount

Group by ID,ID1;

• ###### Re: Load only rows with min value

Fine.

Temp:

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

ID2,

Max(Amount)

Resident Temp Group by ID, ID2;

DROP Table Temp;

• ###### Re: Load only rows with min value

Hi Tamil,

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

Temp:

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

];

• ###### Re: Load only rows with min value

Hi Kushal,

Sorry for the late reply. I started to home so couldn't reply.  I have misunderstood the question. Thanks for pointing my mistake.

• ###### Re: Load only rows with min value

!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 !

• ###### Re: Load only rows with min value

How do you find the diff?

• ###### Re: Load only rows with min value

Data:

ID

DateOne

SETDate

DateTwo,

floor(DateTwo)- floor(DateOne) as Diff

FROM table;

New:

DateOne,

FirstSortedValue(DateTwo,Diff) as DateTwo

Resident Data

group by ID,DateOne;

drop table Data;

Make sure that your Date fields are in proper format

• ###### Re: Load only rows with min value

Thank you for that. There is only one thing missing. I need the SETDate in the New/Final Table...

• ###### Re: Load only rows with min value

Oh sorry. I mean DateTwo

• ###### Re: Load only rows with min value

date2 is already there.. see firstsortedvalue

• ###### Re: Load only rows with min value

Now I see it's my fault. In the table there is a column missing -> Amount. I need Amount in my final table

• ###### Re: Load only rows with min value

Try this,

Data:

DateOne,

SETDate,

DateTwo,

DateOne-DateTwo as Diff,

Amount

FROM

[C:\Users\Tamil\Desktop\TB01_20160520_130436.xls]

(biff, embedded labels, table is Sheet1\$);

Temp:

Inner Keep

DateOne,

Max(Diff) as Diff

Resident Data

Group by ID, DateOne;

DROP Table Temp;

• ###### Re: Load only rows with min value

Do below change in my above post

Add Amount field in Data table

Then add one more field amount in New table as below

firstsortedvalue(Amount,Diff) as Amount

• ###### Re: Load only rows with min value

Data:

ID

DateOne

SETDate

DateTwo,

floor(DateTwo)- floor(DateOne) as Diff,

Amount

FROM table;

New:

DateOne,

FirstSortedValue(DateTwo,Diff) as DateTwo,

Firstsortedvalue(Amount,Diff) as Amount

Resident Data

group by ID,DateOne;

drop table Data;

• ###### Re: Load only rows with min value

Hi Hans,

Try this,

Data:

DateOne,

SETDate,

DateTwo,

DateOne-DateTwo as Diff

FROM

[C:\Users\Tamil\Desktop\TB01_20160520_130436.xls]

(biff, embedded labels, table is Sheet1\$);

Temp:

Inner Keep