I think just a simple ROW_NUMBER() should do it.

SELECT *
FROM (
    SELECT *
        , ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY START_DATE DESC) AS rn
    FROM SomeTable
)t
WHERE rn = 1

Windowed functions to the rescue!

SELECT Employee, StartDate, StopDate
  FROM (
        SELECT *, MAX(StopDate) OVER (PARTITION BY Employee) AS mxStopDate, MIN(StopDate) OVER (PARTITION BY Employee) AS mnStopDate
          FROM @Table
       ) a
 WHERE StopDate = '1753-01-01'
    OR (StopDate = mxStopDate AND mnStopDate <> '1753-01-01')

Here we're using MIN and MAX windowed functions to find the first and last stop dates for each employee. Since we can't reference them in the where clause, we're using them as a subquery, and filtering on the outer one.

It's a bit meaningless to show a date in 1753 as indicating something is in progress - just a NULL here would be preferable since it doesn't have a stop date yet.

Based on your description it would make more sense to just aggregate as follows and just indicate it's actually in progress:

select Employee, 
  Max(Iif(Stop_date = '17530101', 'In Progress', Convert(Varchar(10), Stop_Date, 120)))
from Assignments
group by employee;

This should be safe and sufficiently fast, assuming you have an index on (Employee, StopDate) :

declare @t table (
    Employee int,
    StartDate date,
    StopDate date
);

-- Sample data
insert into @t (Employee, StartDate, StopDate)
values
(51, '2023-07-05', '2023-07-06'),
(51, '2023-07-07', '2023-07-10'),
(51, '2023-07-11', '1753-01-01'),
(32, '2023-07-04', '2023-07-06'),
(32, '2023-07-06', '2023-07-07'),
(32, '2023-07-11', '2023-07-12');

-- The query
select sq.Employee, sq.StopDate
from (
select t.Employee, t.StopDate,
    row_number() over(
        partition by t.Employee
        order by case t.StopDate
            when '17530101' then 1
            else 2
        end, t.StopDate desc
    ) as [RN]
from @t t
) sq
where sq.RN = 1;