Home Forums SQL Server 2008 SQL Server 2008 - General How do I join tables, but select top 1 from 1-many tbl? RE: How do I join tables, but select top 1 from 1-many tbl?

  • October 12, 2012 at 9:57 am

    #1548747

    Don. (10/12/2012)

    Essentially I have a Contact table and a History table.

    I'd like to get the join the tables, but only show the most recent record in History.

    How would I join the tables, but only select the top 1 record from the history table?

    Contact

    Accountno Company, Contact, Recid

    History

    Accountno, LastUser, LastDate, Recid

    Accountno is specific to each contact.

    Recid is unique to each record

    I hope this isnt to to vague

    Thanks for your help.

    SELECT c.*, h.*

    FROM Contact c

    OUTER APPLY (

    SELECT TOP 1 h.*

    FROM History h

    WHERE h.Accountno = c.Accountno

    ORDER BY h.LastDate DESC

    ) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden