Remove rows from table based on column value using self join

Question

I have a requirement where my data looks like below. I have to find the ids from the table where the last pid status is not "removed".

Note:- 1. To get the last pid status use the "date" and "hour" columns. 2. If for an "id", a pid's last "status" value is removed then don't include that row in the result.

id  |   key    |    date     |  hour    | pid  | status
--------------------------------------------------------
id1 |   one    |    20180618 |  2       |  p1  | added
id1 |   one    |    20180618 |  3       |  p1  | removed
id1 |   one    |    20180618 |  4       |  p1  | added
id1 |   one    |    20180618 |  4       |  p2  | added

id1 |   one    |    20180619 |  2       |  p1  | removed
id1 |   one    |    20180619 |  4       |  p1  | added
id1 |   one    |    20180619 |  4       |  p2  | removed
id1 |   one    |    20180619 |  5       |  p3  | added

id2 |   one    |    20180619 |  5       |  p1  | added
id2 |   one    |    20180619 |  5       |  p2  | added
id2 |   one    |    20180619 |  6       |  p1  | removed

Expected output:-

id  |   key    |    date     |  hour    | pid  | status
--------------------------------------------------------
id1 |   one    |    20180619 |  4       |  p1  | added
id1 |   one    |    20180619 |  5       |  p3  | added
id2 |   one    |    20180619 |  5       |  p2  | added

I don't want to delete the data from the source table. I want to query the source table to produce the above result using self join.

Answer 1

Use the row_number() function to identify the latest record for each combination of id and pid and then it's easy to select only those with the status you want, like so:

declare @SampleData table (id varchar(32), [key] varchar(32), [date] date, [hour] int, pid varchar(32), [status] varchar(32));
insert @SampleData values
    ('id1', 'one', '20180618', 2, 'p1', 'added'),
    ('id1', 'one', '20180618', 3, 'p1', 'removed'),
    ('id1', 'one', '20180618', 4, 'p1', 'added'),
    ('id1', 'one', '20180618', 4, 'p2', 'added'),
    ('id1', 'one', '20180619', 2, 'p1', 'removed'),
    ('id1', 'one', '20180619', 4, 'p1', 'added'),
    ('id1', 'one', '20180619', 4, 'p2', 'removed'),
    ('id1', 'one', '20180619', 5, 'p3', 'added'),
    ('id2', 'one', '20180619', 5, 'p1', 'added'),
    ('id2', 'one', '20180619', 5, 'p2', 'added'),
    ('id2', 'one', '20180619', 6, 'p1', 'removed');

with OrderedDataCTE as
(
    select
        S.id, S.[key], S.[date], S.[hour], S.pid, S.[status],
        [sequence] = row_number() over (partition by S.id, S.pid order by S.[date] desc, S.[hour] desc)
    from
        @SampleData S
)
select
    O.id, O.[key], O.[date], O.[hour], O.pid, O.[status]
from
    OrderedDataCTE O
where
    O.[sequence] = 1 and
    O.[status] != 'removed';