SELECT from a table partition by order and status

So I have the following Searches table:

ID  CreatedOn    Closed On    ClosedByUserID
_________________________________________
7 | 22/12/2020 | NULL       |     NULL       -> pending
6 | 21/12/2020 | 22/12/2020 |     NULL       -> outdated
5 | 20/12/2020 | 21/12/2020 |     NULL       -> outdated
4 | 19/12/2020 | 20/12/2020 |      1         -> closed
3 | 18/12/2020 | 19/12/2020 |      1         -> closed
2 | 17/12/2020 | 18/12/2020 |     NULL       -> outdated
1 | 16/12/2020 | 17/12/2020 |     NULL       -> outdated

The status is a combination of ClosedOn and ClosedByUserID:

• Pending: If ClosedOn and ClosedByUserId are null
• Outdated: If ClosedOn is not null and ClosedByUserID is null
• Closed: If ClosedOn is not null and ClosedBYUserID is not null

I am trying to select all records and trying to filter the duplicate outdated records and just show the latest one. I would still want to see multiple closed searches after each other, and there can only be one pending.

From the above example the result expected would be:

ID  CreatedOn    Closed On    ClosedByUserID
_________________________________________
7 | 22/12/2020 | NULL       |     NULL       -> pending
6 | 21/12/2020 | 22/12/2020 |     NULL       -> outdated
4 | 19/12/2020 | 20/12/2020 |      1         -> closed
3 | 18/12/2020 | 19/12/2020 |      1         -> closed
2 | 17/12/2020 | 18/12/2020 |     NULL       -> outdated

I have tried using Partitions to select the rows where Status is Closed or Status is Pending or (Status is Outdated and RN = 1):

;WITH SearchesWithStatus
AS
(
    SELECT  *,
            CASE
                WHEN ClosedByUserId is not null AND ClosedOn is not null THEN 'CLOSED'
                WHEN ClosedByUserId is null AND ClosedOn is not null THEN 'OUTDATED'
                WHEN ClosedByUserId is null AND ClosedOn is null THEN 'OPEN'
            END as SearchStatus,
            ROW_NUMBER() OVER (PARTITION BY CASE
                WHEN ClosedByUserId is not null AND ClosedOn is not null THEN 'CLOSED'
                WHEN ClosedByUserId is null AND ClosedOn is not null THEN 'OUTDATED'
                WHEN ClosedByUserId is null AND ClosedOn is null THEN 'OPEN'
            END ORDER BY CreatedOn DESC) rn
            FROM    Searches
)


SELECT *
FROM    SearchesWithStatus
order by CreatedOn desc

However the result is appearing as follows and RN is not being reset to 1 after the closed record

ID  CreatedOn    Closed On    ClosedByUserID    Status     RN
_______________________________________________________________
7 | 22/12/2020 | NULL       |     NULL        | pending  | 1
6 | 21/12/2020 | 22/12/2020 |     NULL        | outdated | 1
5 | 20/12/2020 | 21/12/2020 |     NULL        | outdated | 2
4 | 19/12/2020 | 20/12/2020 |      1          | closed   | 1
3 | 18/12/2020 | 19/12/2020 |      1          | closed   | 2
2 | 17/12/2020 | 18/12/2020 |     NULL        | outdated | 3
1 | 16/12/2020 | 17/12/2020 |     NULL        | outdated | 4

Is Partition the correct way to approach this? And if so what am i missing?

** updated to have also the scenario where two searches are closed after each other, which i still would want to display

Answer

I am trying to select all records and trying to filter the duplicate outdated records and just show the latest one.

This means that the next record does not have a status of 'OUTDATED' when the current record has that status.

Use lead():

WITH SearchesWithStatus AS (
      SELECT s.*,
             (CASE WHEN ClosedByUserId is not null AND ClosedOn is not null THEN 'CLOSED'
                   WHEN ClosedByUserId is null AND ClosedOn is not null THEN 'OUTDATED'
                   WHEN ClosedByUserId is null AND ClosedOn is null THEN 'OPEN'
              END) as SearchStatus
      FROM Searches s
     )
SELECT ss.*
FROM (SELECT ss.*,
             LEAD(SearchStatus) OVER (ORDER BY id) as next_status
      FROM SearchesWithStatus ss
     ) ss
WHERE SearchStatus <> 'OUTDATED' OR
      next_status IS NULL OR
      next_status <> 'OUTDATED';

Note: I’m not sure if “latest” refers to the id or CreatedOn. The above uses id — the two are equivalent in your sample data.