Select rows with min

[Anonymous]

Hi,

I need help with aggregating my table:

From this table I need only the yellow lines with a minimum in "Diff_Date" in each product-Group. And if there are two min, like product A1, I need both lines.

How can i manage this in my script?

[sunny_talwar]

May be this

Table:

LOAD Diff_Date,

     [Date-1],

     [Date-2],

     Key_Product

FROM ....;

Right Join (Table)

LOAD Key_Product,

     Min(Diff_Date) as Diff_Date

Resident Table

Group By Key_Product;

Updated my response based on fvelascog72 comment who pointed out that i missed Group By statement

[antoniotiman]

LOAD *

From Table

Where Diff_Date <= 1;

[Anonymous]

Thanks, but the smalles number could be any number. Only in the example it is 0 and 1.

[fvelascog72 · Accepted solution]

Hi,

The same solution plus GROUP BY.

Table1:

LOAD * Inline [

Date_1, Date_2, Diff_Date, Key_Product

01/01/2017, 01/01/2017, 1, Product B

02/01/2017, 02/01/2017, 7, Product B

03/01/2017, 03/01/2017, 0, Product A1

04/01/2017, 04/01/2017, 16, Product A1

05/01/2017, 05/01/2017, 0, Product A2

01/02/2017, 01/02/2017, 36, Product A2

02/02/2017, 02/02/2017, 2, Product C

03/02/2017, 03/02/2017, 0, Product A1

];

Right Join

LOAD

  Min(Diff_Date) as Diff_Date,

  Key_Product

Resident Table1

Group by Key_Product;

Saludos

[Anonymous]

Thanks! This works and I learnd something new!