Sort rows by latest group

I have the below dataset which includes 3 columns:

• Inspection Id
• Inspection Date
• Fault Number

Note: I have added in the 2 additional columns 'Comment' and 'Required Sort Order' to help explain what I am trying to do.

What I need is to sort in the order shown in the 'Required sort order' column.

The user does inspections on different dates. We want the first record to be the latest based on Inspection Date. This gives us the first Fault number. We then want all the other records for that fault number next (ordered by inspection date). After all the records for that fault number we want the next fault number with the latest inspection date, and so on.

In effect I want the records to be grouped together by fault number, ordered by the inspection date.

So the result should look like this:

Any hints welcome!

This gives the correct sort:

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;

Outputs:

Also, a little comment regarding the question, you really should provide non-ambigious date formats in text (not screenshots). 9/01/2025 can be both September or January, here it might not matter, but sometimes it does. YYYY-MM-DD is the way to go.