Show unique value for id, based on latest createby date

Question

I am using the following SQL code to JOIN a few tables, but need to show unique values for the s.incidentid based on the latest s.createdate.

I know I need to use a Sub query with Maxdate, but I am not sure on the correct syntax.

This is my first query with multiple joins and I am struggling to get my head round it.

Here is my code:

SELECT 
s.incidentid, 
u.internalid as AssignedTo, 
u.fullname as AssignedTo_FullName, 
s.createby as AssignedBy, 
u2.fullname as AssignedBy_FullName, 
s.createdate as AssignedTime, 
i.[description], 
i.fix,
st.[description] as [Status],
(SELECT (CASE WHEN u.internalid = s.createby THEN 'Yes' ELSE 'No' END) as SelfAssigned),
d.d1,
d.d2,
d.d3,
d.d4,
d.d5

FROM dbo.IncidentServiceLevelAgreement s
JOIN dbo.UserAll u on u.userid = s.userid
JOIN dbo.UserAll u2 on u2.internalid = s.createby
JOIN dbo.IncidentAll i on s.incidentid = i.incidentid
JOIN dbo.[Status] st on i.statusid = st.statusid
JOIN dbo.flatdiagnosis d on i.actualdiagnosisid = d.diagnosisid 
WHERE (s.groupId = '4954' and s.incidentServiceLevelAgreementTypeID = '9')
ORDER BY AssignedTime DESC

Any help greatly appreciated.

Answer 1

The easiest is to use a CTE and the ROW_NUMBER function:

WITH CTE AS
(
    SELECT RN = ROW_NUMBER() OVER ( PARTITION BY incidentid
                                    ORDER BY createdate DESC ),
       s.Incidentid, 
       u.Internalid     AS AssignedTo, 
       u.Fullname       AS AssignedTo_FullName, 
       s.Createby       AS AssignedBy, 
       u2.Fullname      AS AssignedBy_FullName, 
       s.Createdate     AS AssignedTime, 
       i.[Description], 
       i.Fix, 
       st.[Description] AS [Status], 
       SelfAssigned =  CASE WHEN u.Internalid = s.Createby 
                       THEN 'Yes' ELSE 'No' END, 
       d.D1, 
       d.D2, 
       d.D3, 
       d.D4, 
       d.D5 
    FROM   dbo.Incidentservicelevelagreement s 
       JOIN dbo.Userall u 
         ON u.Userid = s.Userid 
       JOIN dbo.Userall u2 
         ON u2.Internalid = s.Createby 
       JOIN dbo.Incidentall i 
         ON s.Incidentid = i.Incidentid 
       JOIN dbo.[Status] st 
         ON i.Statusid = st.Statusid 
       JOIN dbo.Flatdiagnosis d 
         ON i.Actualdiagnosisid = d.Diagnosisid 
    WHERE  ( s.Groupid = '4954' 
         AND s.Incidentservicelevelagreementtypeid = '9' ) 
)
SELECT * FROM CTE WHERE RN = 1
ORDER BY  AssignedTime DESC

(instead of SELECT * list all columns explicitly, I didn't feel like it)