I’m not sure how to explain it so hopefully this example will.
Here is my data:

NOTE: row_num is added for reference only. it is not part of the data

row_num, id, number, version, status, date

1, 99999, CDC800, v8.5, F, 2024-02-05
2 ,99904, CDC800, v8.5, F, 2023-12-07
3, 98923, CDC800, v8.5, F, 2023-10-26
4, 97999, CDC800, v8.5, F, 2023-9-13
5, 96900, CDC800, v8.5, C, 2023-7-31
6, 96844, CDC800, v8.5, F, 2023-03-02
7, 95000, CDC800, v8.5, F, 2022-09-19
8, 97500, CDC800, v6.2, C, 2023-03-20
9, 67900, CDC800, v12.0,F, 2024-01-01
10,88777, CDC800, v2.2, F, 2022-05-15

sorted by number, version, date DESC

rows 1 through 4 need to be a single row with max date
row 5 remains the same
rows 6 and 7 need to be a single row with max date
rows 8, 9, and 10 need to remain the same.

1, 99999, CDC800, v8.5, F, 2024-02-05
5, 96900, CDC800, v8.5, C, 2023-7-31
6, 96844, CDC800, v8.5, F, 2023-03-02
8, 97500, CDC800, v6.2, C, 2023-03-20
9, 67900, CDC800, v12.0,F, 2024-01-01
10,88777, CDC800, v2.2, F, 2022-05-15

When I group by number, version, status it of course includes rows 1 through 5 and 6. I need row 6 to stand alone because the status changed to F for a short while.

If I use window functions such as ROW_NUMBER () OVER (PARTITION number, version, status ORDER BY date) … or some version of that … it just continues to number. Rows 1 through 4 get number 1 through 4 and row 6 gets the numbered 5.

I need fresh eyes. I think I’m over thinking this?