Classificar linhas pelo grupo mais recente

Isso fornece a classificação correta:

SELECT  *
FROM
(
    VALUES  (1, N'1/01/2025', 1, N'Fault1-inspection 1', 7)
    ,   (2, N'2/01/2025', 2, N'Fault2-inspection 1', 3)
    ,   (3, N'3/01/2025', 1, N'Fault1-inspection 2', 6)
    ,   (4, N'4/01/2025', 3, N'Fault3-inspection 1', 9)
    ,   (5, N'5/01/2025', 2, N'Fault2-inspection 2', 2)
    ,   (6, N'6/01/2025', 1, N'Fault1-inspection 3', 5)
    ,   (7, N'7/01/2025', 3, N'Fault3-inspection 2', 8)
    ,   (8, N'8/01/2025', 1, N'Fault1-inspection 4', 4)
    ,   (9, N'9/01/2025', 2, N'Fault2-inspection 3', 1)
) t (InspectionId, InspectionDate, FaultNumber, Comment, [Required sort order])
ORDER BY MAX(CAST(InspectionDate AS datetime))
    OVER (partition BY FaultNumber) DESC, inspectiondate DESC;

Saídas:

Além disso, um pequeno comentário sobre a pergunta: você realmente deveria fornecer formatos de data não ambíguos em texto (não em capturas de tela). 9/01/2025Pode ser setembro ou janeiro; aqui pode não importar, mas às vezes importa. AAAA-MM-DD é o ideal.

Uma primeira abordagem intuitiva consiste em criar sua tabela de referência de "última inspeção para cada falha" (como uma tabela de subseleção, de modo que ela durará apenas a consulta) e,
em seguida, listar sua inspectionstabela com uma pesquisa para essa última inspeção para obter a ordem:

select InspectionId, InspectionDate, i.FaultNumber, Comment
from (select FaultNumber, max(InspectionDate) LastInspection from inspections group by FaultNumber) lasts -- `lasts` is a pseudo-table holding the fault's latest inspection. The group by ensures 1 row per fault.
join inspections i on i.FaultNumber = lasts.FaultNumber
order by LastInspection desc, InspectionDate desc;

Mas com a ajuda das funções de janela você poderá fazer isso no local :

uma janela faz com que todas as linhas "vejam" seus irmãos (relacionados aos mesmos critérios, aqui FaultNumber),
então qualquer linha com FaultNumber2 será capaz de obter o max(InspectionDate)de todas as outras linhas com o mesmoFaultNumber .

select InspectionId, InspectionDate, FaultNumber, Comment
from inspections
order by
    max(InspectionDate) over (partition by FaultNumber) desc,
    InspectionDate desc;

Veja a demonstração em um violino .