• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

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?
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can solve this in numerous ways.
I wrote an article telling the techniques:
Imran Javed ZiaCommented:
you can use following query

SELECT Orders.[Order Number], max(Invoices.InvoiceId), max(Invoices.[Invoice Number])
FROM Orders 
	INNER JOIN Invoices on Invoices.OrdersId = Orders.OrdersId 

Group by Orders.[Order Number]

Open in new window

grwallaceAuthor Commented:
I gave LJZ the most points as this was a direct answer to a direct question, however Angelll also deserves some credit for an excelent article which I will use for refrence in the future

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now