CREATE TABLE PairingActivity (
    PairingId INT,
    PairingDutyId INT,
    PairingActivityId INT,
    EmployeeId INT,
    PairingEventTypeId INT,
    PEStartDate DATETIME,
    FFArrival DATETIME,
    ZuluOffset DECIMAL(5, 2)
);

INSERT INTO PairingActivity (PairingId, PairingDutyId, PairingActivityId, EmployeeId, PairingEventTypeId, PEStartDate, FFArrival, ZuluOffset)
VALUES
    (2010, 682, 247907, 10, NULL, NULL, '2021-06-18 15:18:00', NULL),
    (2010, 682, 247908, 10, NULL, NULL, '2021-06-18 20:15:00', NULL),
    (2010, 682, 247909, 10, 8, '2021-06-18 19:55:00', NULL, 9.0),
    (2010, 683, 247910, 10, NULL, NULL, '2021-06-20 01:55:00', NULL),
    (1946, 980, 225388, 3, NULL, NULL, '2022-03-01 19:28:00', NULL),
    (1946, 980, 225389, 3, 8, '2023-03-01 19:40:00', NULL, 9.0),
    (1946, 981, 225390, 3, NULL, NULL, '2022-03-03 02:31:00', NULL);

  
WITH PairingActivityInPairing AS (
    SELECT *,
        LAG(FFArrival) OVER (PARTITION BY PairingId, PairingDutyId ORDER BY PairingActivityId) AS LagFFArrival
    FROM PairingActivity
)
SELECT *
FROM PairingActivityInPairing
ORDER BY PairingId desc

fiddle

If you include LAG() under the SELECT DISTINCT, DISTINCT will mess up the ORDER BY sequence you put it within LAG() , making the final result non-ordered, which probably is not what you intend to do.

It makes more sense to perform LAG() on the set of unique values after DISTINCT . DISTINCT is an aggregate function that returns the set of unique values so it synthesizes/condenses information. Whereas window function (ie. LAG()) retains individual records.

So

SELECT *, LAG() OVER (PARTITION BY... ORDER BY ...) AS LagFFArrival
FROM (
       SELECT DISTINCT
         all the listed columns
      ) AS sub;