Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Select rows with min

Hi,

I need help with aggregating my table:

Table_origin.png

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.

table_result.png

How can i manage this in my script?

1 Solution

Accepted Solutions
fvelascog72
Partner - Specialist
Partner - Specialist

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

View solution in original post

5 Replies
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
Master III
Master III

LOAD *

From Table

Where Diff_Date <= 1;

Anonymous
Not applicable
Author

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

fvelascog72
Partner - Specialist
Partner - Specialist

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
Not applicable
Author

Thanks! This works and I learnd something new!