Min function - 1 to many relationship

[#1]

In the following code I have a one to many relationship. One [Sales Order] for multiple [Bill To] dates.

I need to find the lowest Bill to date for each sales order.

Sales Order         Bill To

4000003               11/01/2008

4000005               11/01/2009

4000025               11/01/2010

4000085               11/01/2011

4000078               11/01/2012

4000458               11/01/2013

I'm using the following code, but im still receiving multiple Bill To dates for 1 Sales Order.

LEFT KEEP (Main)

LOAD

DOC_NUMBER,

min(date(invoice_date)) as inv_date_sd

FROM source

GROUP BY DOC_NUMBER, invoice_date;

Does anyone have any suggestions?

Thanks

[Jason_Michaelides · Accepted solution]

Is invoice_date the same as "Bill To" date?  If so, remove it from the Group By clause.

Hope this helps,

Jason

[Clever_Anjos]

LEFT KEEP (Main)
LOAD
DOC_NUMBER,
min(date(invoice_date)) as inv_date_sd
FROM source
GROUP BY DOC_NUMBER;

[#4]

That worked, I removed the invoice_date from the Group By clause and it worked correctly.

Why did this resolve the issue?

Thank you both for your help.

[Jason_Michaelides]

Grouping by all fields is the same as not grouping. You want to group the rows by DOC_NUMBER, and from within each group get the minimum invoice date.