• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • 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 ZiaConsultant Software Engineer - .NET ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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