SSRS report builder 3.0. SQL recordset help

Question

I’ve simplified this set of data and here’s my goal. For each EVENT with more than one EMP (Employee), I want just a record set just showing the EVENT, EMP and DATE of just the EMP with the first Date assigned. So, one line for each EVENT listing just one EMP with the first Date assigned.

Event Emp Date
001 SMITH 2021-02-22 11:48:00
001 JONES 2021-02-22 11:35:00
002 BIRD 2021-02-22 12:21:00
002 SMITH 2021-02-22 11:15:00
002 HUNT 2021-02-22 12:39:00
003 JOHNS 2021-02-22 14:29:00
004 SMITH 2021-02-22 14:35:00

So, with Records above, I just want to see:

Event Emp Date
001 JONES 2021-02-22 11:35:00
002 SMITH 2021-02-22 11:15:00
003 JOHNS 2021-02-22 14:29:00
004 SMITH 2021-02-22 14:35:00

Removed should be:

001 SMITH 2021-02-22 11:48:00
002 BIRD 2021-02-22 12:21:00
002 HUNT 2021-02-22 12:39:00

TIA

Answer 1

Hello,

Aggregate on Event to get the first (lowest/min) date and then JOIN, like

;WITH cte AS
    (SELECT Event, MIN(Date) AS MinDate
     FROM yourTable AS T
     GROUP BY Event)
SELECT T.*
FROM yourTable AS T
     INNER JOIN
     cte
         ON T.Event = cte.Event
            AND T.Date = cte.MinDate