Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: select max date record

    Hi,

    i have 2 tables, Patient and Patient_Supplemental:

    Patient:
    Acct_no State Visit_Date PT_ID
    111 RI 01/01/2010 123
    222 RI 01/05/2010 123
    333 RI 01/15/2010 123

    Patient_Supplemental:
    Acct_No State
    111 RI
    222 RI

    I need to join both tables using Acct_No and State and get the record with the max visit_date where the records exist in both tables. In this case i should get:

    PT_ID Acct_No State Visit_Date
    123 222 RI 01/05/2010

    Thanks
    Scott

  2. #2
    Join Date
    Feb 2007
    Posts
    38
    Try this

    SELECT PT_ID, a.Acct_No, a.[State], a.Visit_Date
    FROM Patient a
    JOIN Patient_Supplemental b
    ON a.Acct_No=b.Acct_No and a.[State]=b.[State]
    WHERE a.Visit_Date=(
    SELECT Max(c.Visit_Date)
    FROM Patient c
    JOIN Patient_Supplemental d
    ON c.Acct_No=d.Acct_No and c.[State]=d.[State])

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •