Finding the last item

[alan_grn]

I am trying to create a load script and want to be able to find the last order for a customer.

I want to pass the cutomer id and return the order number of the last order for that customer.

How do I do this?

[#2]

I would go with max(orderdate) and use a group by customer etc. Do you have an example?

[alan_grn]

Thanks this should work if I create a table by loading the max values in to it.

What I am trying to do as part of a load script is for a customer ID (while loading table 2) find the last order for that customer (from table1 whihc has cust Id and order id) and load it in to the table that I am loading. (table 2)

[Oleg_Troyansky]

I'd recommend using function FirstSortedValue to get the last Order, then creating a mapping table, to map CustomerId to theLastOrderID, and use the map when you load your table 2. Something like the following:

LastOrderMap:
mapping load
 CustomerID,
 FirstSortedValue(distinct OrderID, OrderDate * -1) as LastOrder // Multiplying Date by -1 causes the LAST order to be returned
resident
 Table1
group by
 CustomerID
;
Table2:
load
 CustomerID,
 ...
 ApplyMap('LastOrderMap', CustomerID, null()) as LastOrder
resident
 ...

cheers,