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

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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