#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Newbie needing help. Capture most current unique records


    Here is my issue:
    urn visit_no last_name first_name admit_date dis_date
    3105 05424 MOUSE MICKEY 2012-11-15 2012-11-16
    3105 06261 MOUSE MICKEY 2012-11-26 2012-11-28
    3105 00469 MOUSE MICKEY 2011-11-01 2012-11-03
    4204 00047 DUCK DAFFY 2010-12-01 2010-12-05
    4204 00090 DUCK DAFFY 2010-07-01 2010-07-06
    4204 06300 DUCK DAFFY 2012-12-01 2012-12-03


    select P.URN, V.VISIT_NO, P.LAST_NAME, P.FIRST_NAME, V.ADMIT_DATE, V.DISCH_DATE

    from PATIENT P
    inner join VISIT V on P.URN = V.URN

    What I want to end up with is the record with the last admit_date for the unique urn

    3105 06261 MOUSE MICKEY 2012-11-26 2012-11-28
    4204 06300 DUCK DAFFY 2012-12-01 2012-12-03


    Thanks for any help/sugguestions
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    Code:
    SELECT p.urn
         , v.visit_no
         , p.last_name
         , p.first_name
         , v.admit_date
         , v.disch_date
      FROM patient AS p
    INNER 
      JOIN ( SELECT urn
                  , MAX(admit_date) AS last_admit_date
               FROM visit 
             GROUP
                 BY urn ) AS m 
        ON m.urn = p.urn
    INNER 
      JOIN visit AS v 
        ON v.urn = m.urn
       AND v.admit_date = m.last_admit_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Perfect - You and the Code


    Thanks so much. This go me exactly what I was hoping for.

IMN logo majestic logo threadwatch logo seochat tools logo