Query to get oldest record

Question

i have this following code:

;with cte as (
   Select RegardingObjectId , 
          DATEADD(mi, DATEDIFF(mi, getutcdate(), getdate()),CreatedON) as [Time First Response] 
          from dbo.Email
    where DirectionCode = '1'
)
SELECT  dbo.Incident.new_CaseID as [CASE ID], 
        cte.RegardingObjectId, 
        dbo.Incident.IncidentId, 
        dbo.Incident.Title as [SUBJECT], 
        dbo.Incident.StatusCode as [STATUS], 
        cte.[Time First Response] 
from dbo.Incident
left Join cte on cte.RegardingObjectId = dbo.Incident.IncidentId
order by dbo.Incident.new_CaseID desc

this query will return a result like :

As you can see, the query returning multiple records with same CASE ID. What i want is, for every multiple records have same CASE ID, only one with oldest [Time First Response] is retrieved

Answer 1

You can wrap your query in another CTE and use ROW_NUMBER to prioritize and get the oldest record per [CASE ID]:

;with cte as (
   Select RegardingObjectId , 
          DATEADD(mi, DATEDIFF(mi, getutcdate(), getdate()),CreatedON) 
             as [Time First Response] 
          from dbo.Email
    where DirectionCode = '1'
), cte2 as (
   SELECT  dbo.Incident.new_CaseID as [CASE ID], 
           cte.RegardingObjectId, 
           dbo.Incident.IncidentId, 
           dbo.Incident.Title as [SUBJECT], 
           dbo.Incident.StatusCode as [STATUS], 
           cte.[Time First Response],
           ROW_NUMBER() OVER (PARTITION BY dbo.Incident.new_CaseID
                              ORDER BY cte.[Time First Response]) AS rn
   from dbo.Incident
   left Join cte on cte.RegardingObjectId = dbo.Incident.IncidentId
)
SELECT [CASE ID],
       RegardingObjectId,
       IncidentId,
       [SUBJECT], 
       [STATUS], 
       [Time First Response]
FROM cte2
WHERE rn = 1
ORDER BY [CASE ID] desc