bosstone75 bosstone75 - 2 months ago 8
SQL Question

Pull up the most recent record including joining 2 tables and filters

I have seen a lot of posts on pulling up the most recent record. I haven't been able to find one that includes joining another table and filters.

What I need is information regarding the most recent document (record) created, but only if it meets certain criteria. PLUS I need to pull in some data from another table.

s504Plans Table

Student ID | Firstname | Startdate | Status
---------- --------- --------- ------
111111 Johnny 1/5/2015 F
222222 Sue 4/7/2016 I
333333 Barb 2/5/2016 F
111111 Johnny 2/1/2016 F


Cases Table

Student ID | School |
---------- ------
111111 Franklin
222222 Eisenhower
333333 Franklin


And the results I'd like to see are only the most recent document where the status of the document is F...

Student ID | Firstname | Startdate | Status | School
---------- --------- --------- ------ ------
111111 Johnny 2/1/2016 F Franklin
333333 Barb 2/5/2016 F Franklin


Thanks!

Answer

You can use inner join and where

select 
    a.Student_ID
  , a.Firstname
  , a.Startdate
  , a.Status
  , b.School
from s504Plans  as a
inner join Cases  as b on  a.Student_ID = b.Student_ID
inner join ( select Student_ID, max(Startdate ) as max_startdate
            from s504Plans
            group by Student_ID) t 
           on ( a.Student_id = t.Student_id and a.Startdate = t.max_startdate)
where a.Status = 'F'