How to pull the most recent transaction based on entry date?

[suexicano]

Hi -

Thanks for your help with this one.

I have a table that records transactions for individual invoices, and I want to pull the most recent transaction based on the entry date. Using the example below, I want to write a SQL statement that would only pull records that have ID 3,4, and 9 (as those records have the most recent EntryDate for each individual Invoice). Here is an example of the transaction table:


ID InvoiceNo EntryDate TransactionCode
------- ------------ ----------- -------------
1 000024832 8/30/2010 00038
2 000024832 9/1/2010 00055
3 000024832 11/20/2010 00078
4 000037852 11/1/2010 00024
5 000037852 10/30/2010 00124
6 000037852 8/7/2010 00226
7 000037852 8/5/2010 00365
8 000042783 9/7/2010 00038
9 000042783 10/4/2010 00356
10 000042783 8/20/2010 00226

[ck9663]

Try:

select ID, max(EntryDate) from YourTable group by ID

Happy Coding!!!

~~ CK

[suexicano]

Thank you for your response, but I forgot to mention that I wanted to pull the EntryDate, InvoiceNo, AND TransactionCode . I have tried the code below, but it pulls each record.

SELECT InvoiceNo, MAX(EntryDOS), TransactionCode
FROM DbTable
GROUP BY InvoiceNo, TransactionCode

[ck9663]

Try this:

with most_recent_record
as
(
select ID, max(EntryDate) as latest_entry_date from YourTable group by ID
)
Select * from YourTable a
where exists (select 1 from most_recent_record b where a.id = b.id and a.entrydate = latest_entry_date)

Happy Coding!!!

~~ CK