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 3:12 pm

    #1548847

    DiverKas (10/12/2012)

    For older environments or other than T-SQL flavor, more generically would be something like (untested):

    SELECT c.Accountno,C.Company,C.Contact,C.Recid,H.lastuser,H.lastdate

    FROM contact C

    LEFT JOIN history h ON C.accountno=h.accountno

    LEFT JOIN (SELECT accountno,MAX(lastdate) AS lastdate

    FROM history

    GROUP BY accountno) mx ON h.accountno=mx.accountno

    AND h.lastdate=mx.lastdate

    Thanks for the reply, unfortunately it doesnt work. 🙁

    Since h.accountno returns 1 of each accountno, its not limiting the h.accountno results to only one record per accountno and all history records are being returned.

    If we could do that with the recid field, it should work, although I currently have no clue how to do that.