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

Hi Alan

What defines "last"? Order date? If so, something generally like this might work - I'm guessing at some of this, since I couldn't quite work out the file structures - sorry!

with lastorder as (select customer, max(orderdate) from filea group by customer) select * from filea join xx on filea.customer = xx.customer and filea.orderdate = xx.orderdate

Modify that in some fashion to work with your file or files.

You could also do it like this -

with lastorder as (select customer, max(orderdate) from filea group by customer) select * from filea where (customer, orderdate) in (select customer,.orderdate from xx)

There also might be ways using a descending order by and use a "fetch 1 rows only" clause - something like that - going by memory. This would be in the CTE xx.

HTH
Vern

On 6/15/2016 11:27 AM, Alan Shore wrote:

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