Re: Obtaining the "last" record in a group via SQL

Hi Alan,

LATERAL joins are typically, but not always, the fastest performing method
to get information like that. IIRC, V5R4 supports them. They are very
simple and flexible to use, once you wrap your head around them initially.

select C.CUSTOMER_ID, O.ORDER_DATE as LAST_CUSTOMER_ORDER_DATE
from CUSTOMER C
cross join lateral (
select O.ORDER_DATE
from ORDER O
where O.CUSTOMER_ID = C.CUSTOMER_ID
order by O.ORDER_DATE desc
fetch first row only
) as O

Or, if you want to include customers without orders:

select C.CUSTOMER_ID, O.ORDER_DATE as LAST_CUSTOMER_ORDER_DATE
from CUSTOMER C
left join lateral (
select O.ORDER_DATE
from ORDER O
where O.CUSTOMER_ID = C.CUSTOMER_ID
order by O.ORDER_DATE desc
fetch first row only
) as O on 1 = 1

LATERAL joins allow you to reference columns in a joined to sub-query that
were added to the result set prior to the sub-query.

Mike

date: Wed, 15 Jun 2016 16:27:11 +0000

from: Alan Shore <ashore@xxxxxxxx>
subject: Obtaining the "last" record in a group via SQL

Hi everyone
Before I forget - we are on V5r4 - soon to upgrade (to at least V7r1 -
yaaaaaay)

Here is my question
II am looking at 2 files
The first file contains 2 fields
Customer number
Data from the last order placed by that customer number
So for this first file - customer number is unique

A logical (by customer number) of orders placed
So for the second file - there can be 1 to many records per customer number

My question is :-
Is there an "EASY" way via sql to get the LAST record for the individual
customer number
Please note that "EASY" is stressed, upper case, underlined , bold

I will probably need to create a program - but just wondered if anyone had
any procedures/proceses/tricks to get that last record from a group

As always - all answers welcome with gratitude
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill