How to select a group or record?

Question

I have table:

How can I select group of record contain status=open? (Group record with status = sent, received, open, close) and delete the rest.

Thank you. MN

Answer 1

Here is a query. Note that the first part is only to set up the test data. Normally, we prefer that you do this yourself to save our time. We cannot copy from an image, but if you give us a script, we can easily work with that.

DECLARE @T TABLE (ID   int NOT NULL PRIMARY KEY,
                  Status varchar(10) NOT NULL)
INSERT @T (ID, Status)
  VALUES(1, 'Sent'), (2, 'Whatever'), (3, 'Extra'), (4, 'Close'),
        (5, 'Junk'), (6, 'Close'), (7, 'Seven'),
        (8, 'Sent'), (9, 'Nove'), (10, 'Kymmenen'), (11, 'Close'),
        (12, 'Douze')

SELECT a.*
FROM   @T a
OUTER  APPLY (SELECT TOP(1) b.Status
              FROM   @T b
              WHERE  b.ID < a.ID
                AND  b.Status IN ('Sent', 'Close')
              ORDER  BY b.ID DESC) AS b
WHERE  'Sent' IN (a.Status, b.Status)
      

Answer 2

First using filter then select