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)?

By "Microsoft SQL", do you mean **SQL Server** (if so, please add a `sql-server` tag), or do you mean "SQL in Microsoft Access" (if so, please add a `ms-access` tag) — marc_s, Apr 21 '16 at 04:42

Boyd P · Answer 1 · 2016-04-20T22:46:41.063

This should get you what you need. I don't know if there's a performance hit doing this via nested subquery, but I like the clean syntax of this:

SELECT 
   s.studentid, 
   s.name, 
   LastCommDate = MAX(c.comm_date), 
   LastCommMessage = (SELECT comm_msg FROM Communications WHERE studentid = s.studentid AND comm_date = MAX(c.comm_date))
FROM Student AS s 
INNER JOIN Communications AS c
ON s.studentid = c.studentid
GROUP BY s.studentid, s.name

score 0 · Accepted Answer · answered Apr 20 '16 at 22:41

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.

Microsoft SQL Server : max date joining two tables

2 Answers2