grwallace
asked on
Join on only the last record in a table
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?
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?
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER