max/last query problem

[eddy]

This is my problem.

I have a table that tracks the status of an invoice. It can be that an invoice goes through 5-10 status' before it is paid. So let's say, first time, it's on call, second time collection, third time in payment, forth time needs to be verified that it paid, etc...

Table has the id, invoiceid, collector, date, idofdescription.

Now what I want is to see the most recent transaction for all invoices.

I did a group by invoiced and a max of date. That worked fine to get the max date of transaction for each id. The problem is I need to see the idofdescription in the same query. If I put max and a paying file went back into collection, then I see paying still and not collection. How do I make the idofdescription DEPENDANT on the max date for that invoice??

 

[pbaldy]

2 queries. The first you already have, that gets the max date per ID. Start a second query and join the original table to that first query on those 2 fields. That should enable you to add other records from that table.