Join on only the last record in a table

Posted on 2011-05-04
Last Modified: 2012-05-11
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?
Question by:grwallace
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    you can solve this in numerous ways.
    I wrote an article telling the techniques:
    LVL 16

    Accepted Solution

    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


    Author Closing Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    delete duplicate data 4 42
    Incremental load example 2 28
    TSQL Update Field Older Than 5 Days 10 36
    Isolation level in SQL server 3 30
    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now