Sql Server - Get most recent item from a table using sub-select

Recently I needed to create a kinda complex sql query to get the most recent note that had been added to a case in an internal application at work. This is something I come across infrequently enough to forget it every time I want to do it so I thought I would post it as it may be of some use to others (and me next time I need it!)

The requirement: We need to get the latest case note entry made by a user not the system. Data required is date, time, user ID, subject and content fields.

The database structure:

Case Table

Service Table

The solution

It appears fairly straightforward at first glance. Just join across the three tables using primary keys and foreign keys. The complication comes in that we only want to see the most recently added note on a case.

So, I used a sub-select to get the most recently created note ( max(CreateDateTime) ) and compare it to the CreateDateTime in the original select statement. It’s probably easier to look at the sql……

DECLARE @Service varchar(20) SET @Service = ‘MyServiceName’

SELECT attach.Service, attach.CreateDateTime, attach.pxLinkedRefFrom, work.Id, attach.NoteTitle, attach.NoteContent, idx.Status FROM myCaseTable work JOIN myNoteTable attach ON work.Id = attach.CaseId LEFT OUTER JOIN myServiceTable idx ON idx.CaseId = attach.CaseId WHERE attach.CreateDateTime = (

SELECT MAX(CreateDateTime) FROM myNoteTable attach2 WHERE attach2.CaseId = attach.CaseId AND attach2.CreateOperator <> 'System' AND attach2.Service = @service)

Any feedback, especially more performant ways of achieving the same thing task welcome.