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

Here is another way to archive your target:

DECLARE @Table TABLE (
	[ID] int NOT NULL,
	[Status] varchar(10) NOT NULL,
	[Type] char(1) NOT NULL
);

INSERT INTO @Table VALUES 
(1, 'sent', 'P'), (2, 'received', 'P'), (3, 'open', 'P'), (4, 'close', 'P'),
(5, 'sent', 'P'), (6, 'received', 'P'), (7, 'close', 'P'), 
(8, 'sent', 'P'), (9, 'received', 'P'), (10, 'open', 'P'), (11, 'close', 'P'),
(12, 'sent', 'P'), (13, 'received', 'P'), (14, 'close', 'P');

;WITH CTE_RowNumber AS (
	SELECT *, ROW_NUMBER() OVER(ORDER BY [ID]) AS [RowNumber]
	FROM @Table
),
CTE_Matched_Sent AS (
	SELECT [ID], [Status], [Type], [RowNumber]
	FROM (
		SELECT *,
			CASE WHEN [Status] = 'sent' THEN 1 ELSE 0 END AS [Col1],
			CASE WHEN LEAD(Status, 1, 0) OVER (ORDER BY [RowNumber]) = 'received' THEN 1 ELSE 0 END AS [Col2], 
			CASE WHEN LEAD(Status, 2, 0) OVER (ORDER BY [RowNumber]) = 'open' THEN 1 ELSE 0 END AS [Col3],
			CASE WHEN LEAD(Status, 3, 0) OVER (ORDER BY [RowNumber]) = 'close' THEN 1 ELSE 0 END AS [Col4]
		FROM CTE_RowNumber
	) AS temp
	WHERE [Col1] + [Col2] + [Col3] + [Col4] = 4
)

SELECT c1.[ID], c1.[Status], c1.[Type] 
FROM CTE_RowNumber AS c1 
LEFT JOIN CTE_Matched_Sent AS c2 
	ON c1.[RowNumber] = c2.[RowNumber] 
   OR (c1.[RowNumber] = c2.[RowNumber] + 1 AND c1.[Status] = 'received') 
   OR (c1.[RowNumber] = c2.[RowNumber] + 2 AND c1.[Status] = 'open') 
   OR (c1.[RowNumber] = c2.[RowNumber] + 3 AND c1.[Status] = 'close')
WHERE c2.[RowNumber] IS NOT NULL;

Output:

Answer 3

Result will be:

Answer 4

create table test (ID int, status varchar(8), type char(1))
insert into test values
(1,'sent','p'),
(2,'received','p'),
(3,'open','p'),
(4,'close','p'),

(5,'sent','p'),
(6,'received','p'),
(7,'close','p'),
(8,'sent','p'),

 
(9,'received','p'),
(10,'open','p'),
(11,'close','p'),
(12,'sent','p'),
(13,'received','p'),
(14,'close','p')

;with mycte as (
select *, 
sum(case when status='sent' then 2 
when status='received' then 4
when status='open' then 8
when status='close' then 16
else null
end ) over(order by ID) bitand

from test)

select ID,status,type  
from mycte
where 2&bitand =2

drop table test

Answer 5

I updated the query in the next answer. Not sure how to delete this one.