How to get the last record of the month?

[dpoon2000]

Kettle is an ETL tool so I am experimenting and try to extract some data from an Oracle database. I'd like to get the last record for each month as given by the transaction date.

In Database:

Year, Mth, Employee, Status, Transaction Date
2006, 04, John Smith, Hired, 04-30-2006
2007, 10, John Smith, Chg Dept, 10-20-2007
2008, 06, John Smith, Chg Dept, 06-22-2008
2008, 06, John Smith, Resigned, 06-22-2008
2008, 06, John Smith, Terminated, 06-23-2008
2008, 07, Peter Fleming, Hired, 07-29-2008
2008, 07, Peter Fleming, Resigned, 07-30-2008

Desired Extraction:

Year, Mth, Employee, Status, Transaction Date
2006, 04, John Smith, Hired, 04-30-2006
2007, 10, John Smith, Chg Dept, 10-20-2007
2008, 06, John Smith, Terminated, 06-23-2008
2008, 07, Peter Fleming, Resigned, 07-30-2008

In the above example you see that in 2008-06, if there're 3 transactions I want to only get the latest transaction only. Same for 2008-07. The "Desired Extraction" contain only the records with the latest last update date for each unique record (the key is Employee).

Is there a way?

[christophWeißenborn]

FROM ... as OuterTbl
WHERE TransactionDate = (SELECT MAX(TransactionDate) FROM ... as InnerTbl WHERE OuterTbl.Year = InnerTbl.Year AND OuterTbl.Mth = InnerTbl.Mth)

Maybe you should also add an index for better performance of this query.

Regards,
Christoph

[dpoon2000]

I did this and it works. For Oracle, it's:

select * from table1 outertbl
where wid = (select max(wid) from table1 innertbl where outertbl.wid = innertbl.wid);

Thanks,
Daniel...

[MattCasters]

Specify an ORDER BY clause in the database lookup step. Set it to "ORDERDATE DESC" or something similar.
The lookup step will only read the first row (last in this case), descarding the rest.