Finding the most recent record in groups of records 2

[Macoladownunder]

Sorry guys, I have looked at a lot of threads, can't find the answer to this one.

I have the following records:

Cust No Job No Inv No Date
1225 sam0405 36598 09/05/04
1566 des0304 36500 10/05/04
1225 jim0506 36600 11/05/04 *
1566 bob0507 36550 10/06/04 *

The result I want is the 4 attributes of last invoice raised for each customer. The ones with aterisks at the side in this example.

All offers gratefully accepted.



MacolaDownUnder
Aussies using Macola

 

[SQLScholar]

i cant garentee this but try

select 
cust no,
max(job no),
max(Inv No),
max(date)

group by cust no

It might work... it might not do what you want... it might not work... but you never know.

Dan


----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[dbomrrsm]

select test.CustNo, JobNo, InvNo ,Date
from MYTABLE test
join (select CustNo, max(Date) as LastUpdated
from MYTABLE
group by CustNo) MaxDate
on test.CustNo = MaxDate.CustNo
and test.Date = MaxDate.LastUpdated

[blue]DBomrrsm[/blue]