Join on only the last record in a table
Posted on 2011-05-04
I need to create a query from a sales order table linked to an invoice table which returns only the last invouce for the order. It is possible for an order to be invoiced then credited again then re-invoiced giving 3 entries in the Invoice table for one Order. I can easily excluse the Credit leaving 2 entries in the Invoice table but I only wish to return the most recent invoice, which wil always have the highest Id
If I say
SELECT Orders.[Order Number], Invoices.InvoiceId, Invoices.[Invoice Number] FROM Orders INNER JOIN Invoices on Invoices.OrdersId = Orders.OrdersId
it will return the three records.if there are three records present. I would like to only return the one with the highest Invoices.InvoiceId
Any ideas anyone?