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 14, 2012 at 2:53 am

    #1548936

    Don. (10/13/2012)

    Hi Guys,

    Unfortunately neither of those scripts worked as there are multiple identical accountno and lastdate fields. Ideally Id need to filter on the History tables Recid field.

    I've dummied up some data for you to look at

    CREATE TABLE #CONTACT

    (

    ACCOUNTNOVARCHAR(5),

    COMPANYVARCHAR(50),

    CONTACTVARCHAR(50),

    RECIDVARCHAR(5)

    )

    CREATE TABLE #HISTORY

    (

    ACCOUNTNOVARCHAR(5),

    LASTUSERVARCHAR(8),

    LASTDATEDATE,

    RECIDVARCHAR(5)

    )

    INSERT INTO #CONTACT (ACCOUNTNO, COMPANY, CONTACT, RECID)

    SELECT 'C1', 'ACME', 'WILEY COYOTE', 'CR1' UNION ALL

    SELECT 'C2', 'Beta Ltd', 'Joe Soap', 'CR2'

    INSERT INTO #HISTORY (ACCOUNTNO, LASTUSER, LASTDATE, RECID)

    SELECT 'C1', 'JOE', '20120101', 'HR1' UNION ALL

    SELECT 'C1', 'BOB', '20120101', 'HR2' UNION ALL

    SELECT 'C1', 'JOE', '20120201', 'HR3' UNION ALL

    SELECT 'C2', 'JOE', '20120202', 'HR4' UNION ALL

    SELECT 'C2', 'BOB', '20120202', 'HR5'

    How would I get a History select script to return HR1 and HR4?

    Thanks

    Don. (10/12/2012)

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

    Why HR1? HR3 is on the most recent row. Assuming you mean HR3, then this works against the sample data set;

    SELECT c.*, h.*

    FROM #Contact c

    INNER JOIN (

    SELECT h1.Accountno, h1.LastDate, RECID = MIN(h1.RECID)

    FROM #History h1

    INNER JOIN (

    SELECT Accountno, MAX(LastDate) LastDate

    FROM #History

    GROUP BY Accountno

    ) l

    ON l.Accountno = h1.Accountno

    AND l.LastDate = h1.LastDate

    GROUP BY h1.Accountno, h1.LastDate

    ) h

    ON h.Accountno = c.Accountno

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]