You can simply use the ROW_NUMBER function in your CTE, like this:
Declare @diary TABLE ([Diary Reference] int, [Tenant Code] varchar(7), [Category] varchar(4), [Action Date] date, [Follow on Date] date, [Action Code] varchar(4), [Follow on Ind] varchar(5), [Created Date] date, [Created Time] varchar(7) )
INSERT INTO @diary ([Diary Reference],[Tenant Code], [Category], [Action Date], [Follow on Date], [Action Code], [Follow on Ind], [Created Date], [Created Time])
VALUES
(236774,'42441', 'RARP', '2017-02-06', '2017-04-26', 'MARP', 'Taken', '2018-01-26', '19:51'),
(236773,'42441', 'RARP', '2017-07-17', '2017-08-15', 'RPSB', 'Taken', '2018-01-26', '19:49'),
(234006,'42441', 'RARP', '2017-12-22', '2018-01-09', 'RPSO', 'Taken', '2017-12-22', '8:41'),
(235852,'42441', 'RARP', '2018-01-10', NULL, 'RSOA', 'None', '2018-01-10', '11:19'),
(236772, '54482', 'RARP', '2017-10-26', '2018-01-26', 'RPSO', 'Taken', '2018-01-26', '19:20'),
(235174, 37702, 'RARP', '2018-01-05', '2018-01-05', 'RPSO', 'Taken', '2018-01-05', '12:09'),
(235175, 37702, 'RARP', '2018-01-05', '2018-01-08', 'RPSB', 'Taken', '2018-01-05', '12:11'),
(235784, 37702, 'RARP', '2018-01-09', NULL, 'RPSO', 'None', '2018-01-09', '16:35'),
(236775, 37702, 'RARP', '2017-11-16', '2018-01-10', 'RPSB', 'Taken', '2018-01-26', '22:42');
with
dates(Month_End) as (
select cast('2014-01-01' as datetime)-1 as datetime
union all
select DATEADD(month, 1, Month_End+1)-1
from dates where Month_End < GETDATE()),
Months As (
select
DateAdd(month, -1, DateAdd(day, 1, Month_End)) As Month_Start,
Month_End,
ts.[Diary Reference],
ts.[Tenant Code],
ts.Category,
ts.[Action Code],
ts.[Action Date],
ts.[Follow on Date],
ts.[Created Date],
ROW_NUMBER() OVER (PARTITION BY ts.[Tenant Code], DateAdd(month, -1, DateAdd(day, 1, Month_End)) ORDER BY ts.[Action Date] DESC, ts.[Follow on Date] DESC) AS RowNo
FROM
@diary ts
join
dates d ON d.Month_End Between [Action Date] and DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,isnull(ts.[Follow on Date], GETDATE()))+1,0)))
Select Month_Start,Month_End,months.[Tenant Code],[Diary Reference],Category,months.[Action Date],[Follow on Date],[Action Code]
From Months
where RowNo = 1
order by Month_End, [Tenant Code], [Action Date]
