Query to get the oldest record

advertisements

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


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