Distinct result problems - SQL Server

Question

I have 3 tables (Incident, phonecallevent, eventreason)

The incident table stores all the information related to an incident and the ponecallevent table stores the information regarding the calls made to report incidents. The problem is that for one incident there may be several calls, to give you a small example of what I currently need I have this query: (UID: Incident / Name: Reason for call)

SELECT DISTINCT inc.uid, er.Name
FROM [Incident] Inc INNER JOIN
[PhoneCallEvent] PCE ON PCE.IncidentRefs = INC.GID INNER JOIN
[EventReason] ER ON ER.GID = PCE.eventReasonGID

Example result:

UID	NAME
INC 15128238	Without energy
INC 15128237	Without energy
INC 15128248	Intermittent service
INC 15128248	Existence of Danger
INC 15128248	Without energy

The problem is that the incident INC 15128248 has 3 calls and in all 3 of them a different reason was reported. In those cases in which there is more than one reason for an incident, I need to show only the first one and avoid repeating the incident several times .

The result should look like this:

UID	NAME
INC 15128238	Without energy
INC 15128237	Without energy
INC 15128248	Intermittent service

Any ideas?

Do you have more columns to determine which row came first? Show the tables schema and add data samples — Horaciux, Apr 26 '23 at 18:01

score 0 · Answer 1 · answered Apr 26 '23 at 17:58

Using a common table expression (or CTE) and ROW_NUMBER() window function

   WITH OrderedReasons AS (
        SELECT
            inc.uid,
            er.Name,
            ROW_NUMBER() OVER (PARTITION BY inc.uid ORDER BY pce.EventDateTime) AS RowNum
        FROM
            [Incident] inc
            INNER JOIN [PhoneCallEvent] pce ON pce.IncidentRefs = inc.GID
            INNER JOIN [EventReason] er ON er.GID = pce.eventReasonGID
    )
    SELECT
        uid,
        Name
    FROM
        OrderedReasons
    WHERE
        RowNum = 1;

score 0 · Answer 2 · answered Apr 26 '23 at 18:16

you can use row_number() window function with common table expression. To get the earliest call you need to order your data. In place of YourOrderByCOlumnName you can use call datetime column or something like that. Below query will return one row per inc.uid :

with cte as
(SELECT DISTINCT inc.uid, er.Name,row_number()over(partition by inc.uid order by YourOrderByCOlumnName)rn
FROM [Incident] Inc INNER JOIN
[PhoneCallEvent] PCE ON PCE.IncidentRefs = INC.GID INNER JOIN
[EventReason] ER ON ER.GID = PCE.eventReasonGID
)
select uid,name from cte where rn=1

Distinct result problems - SQL Server

2 Answers2