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

Column name	Type
Id (PK)	int

Note table

Column name	Type
Id (PK)	int
CreateDateTime	datetime
CaseId (FK)	varchar(50)
CreateOperator	varchar(50)
NoteTitle	varchar(100)
NoteContent	text
Service	varchar(50)

Service Table

Column name	Type
Id (PK)	int
CaseId (FK)	varchar(50)
Status	varchar(100)

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.