Microsoft SQL Server : max date joining two tables

Question

I am attempting to join two tables while pulling the max date from one. I have a student table and a communication table. Each student is unique in the student table and has many communication entries.

I would like to create a SQL script that pulls each student's ID, name, latest communication date, and the communication message for that date.

I am able to pull the latest date for each student using max(comm_date) and group by, but things get messy (many duplications) when pulling the corresponding communication message.

Table: Student

studentid, name

Table: Communications

studentid, comm_date, comm_msg

Result:

student.studentid, student.name, communications.comm_date, communications.comm_msg

How can I pull the corresponding communication message given max(comm_date)?

Answer 1

This should get what you need...

select
      s.studentid,
      s.name,
      c2.comm_date,
      c2.comm_msg
   from
      Student s
         LEFT JOIN 
         ( select 
                 c1.studentid,
                 max( c1.comm_Date ) as MaxDate
              from
                 Communications c1
              group by
                 c1.studentid ) PreMax
            on s.studentid = PreMax.StudentID
            LEFT JOIN Communications c2
               on PreMax.StudentID = c2.StudentID
              AND PreMax.MaxDate = c2.comm_Dat

Additionally, I would suggest adding a column to your student table for most recent communication date (or even an ID if communications has an auto-increment column such as multiple entries on the same day). Then, via an insert trigger to the communications table, you update the student table with that newest date (or communication ID). Then you never need to keep requerying the MAX() and re-joining multiple times as this one does.