Check for condition in GROUP BY?

Take this example data:

ID    Status    Date
1     Pending   2/10/2020
2     Pending   2/10/2020
3     Pending   2/10/2020
2     Pending   2/10/2020
2     Pending   2/10/2020
1     Complete  2/15/2020

I need an SQL statement that will group all the data but bring back the current status. So for ID 1 the group by needs a condition that only returns the Completed row and also returned the pending rows for ID 2 and 3.

I am not 100% how to write in the condition for this.

Maybe something like:

SELECT ID, Status, Date
FROM table
GROUP BY ID, Status, Date
ORDER BY ID

The problem with this is the resulting data would look like:

ID    Status    Date
1     Pending   2/10/2020
1     Complete  2/15/2020
2     Pending   2/10/2020
3     Pending   2/10/2020

But I need:

ID    Status    Date
1     Complete  2/15/2020
2     Pending   2/10/2020
3     Pending   2/10/2020

What can I do to check for the Completed status so I can only return Completed in the group by?

Answer

To use Date as ‘last row indicator’, you can:

DECLARE @Src TABLE (
    ID int,
    Status varchar(20),
    Date Date
)

INSERT @Src VALUES
(1, 'Pending' ,'2/10/2020'),
(1, 'Complete' ,'2/15/2020'),
(2, 'Pending' ,'2/10/2020'),
(3, 'Pending' ,'2/10/2020');

SELECT TOP 1 WITH TIES *
FROM @Src
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC)

Result:

ID          Status               Date
----------- -------------------- ----------
1           Complete             2020-02-15
2           Pending              2020-02-10
3           Pending              2020-02-10