I am tracking comments made on applications using dense rank function. I only want to include the last comment made. In some applications it may be the Dense Rank = 4 or in some cases it might be Dense Rank = 12.
I am using the following code:
AppTrack
AS
(
SELECT C.application_id
,TI.track_desc
,AT.notes
,DENSE_RANK() OVER (PARTITION BY CA.application_id ORDER BY TI.track_desc) AS DRANK
FROM creditapp AS C
LEFT JOIN app_track AS AT ON C.application_id = AT.application_id
LEFT JOIN track_item AS TI ON AT.track_item_id = TI.track_item_id
FULL OUTER JOIN AppTrack AS CTE3 ON C.application_id = CTE3.application_id AND CTE3.DRANK = 1