sql server - Any cleaner or alternate way to write sql join script -

i have following tables:

 table2         studentnumbers  examtype  ------------------------            1234         1            2343         2               3345         5            3454         1            5465         2             ...   table1     studentnumbers  examtype   examdate  school  area             info  ------------------------------------------------------------------            1234         1       0825  warren    ny    0x504b03042d0            1234         1       0829   north    nj    0x63d86e1ffff            1233         2       0921   north    nj    0xa001400646f            2343         1       0922  warren    ny    0x01400646174            2343         1       0925   north    ny    0x100100070se             ... 

i trying write query following results:

studentnumbers  examtype   examdate  school  area            info -----------------------------------------------------------------           1234         1       0829   north    nj   0x63d86e1ffff           2343         1       0925   north    ny   0x100100070se 

i wrote following query:

select t1.studentnumbers, t1.examdate, t1.school, t1.info, t1.examtype         table1 t1   join(        select ts.studentnumbers, max(ts.examdate) examdate        table2 ts          join table1 pl on             ts.studentnumbers = pl.studentnumbers ts.examtype = pl.examtype        group ts.studentnumbers        ) t2 on t1.studentnumbers = t2.studentnumbers                     , t1.examdate = t2.examdate 

above query works , gives me result want max examdate particular examtype based on table2. using multiple joins best way in aggregate method? or there cleaner option.

your query good, think use table2 table outside subquery better option.

select * (       select studentnumbers, examtype, max(examdate) examdate       table1       group studentnumbers, examtype       ) t1 join table1 t2 on t1.studentnumbers = t2.studentnumbers              , t1.examtype = t2.examtype , t1.examdate = t2.examdate exists(              select 1              table2 t3              t1.studentnumbers = t3.studentnumbers                , t1.examtype = t3.examtype              )