How to select a group or record?

NguyenM2 25 Reputation points

I have table: User's image

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,591 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
817 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 100.7K Reputation points MVP

    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.

                      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)
    1 person found this answer helpful.
    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points

    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
    (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 (
    	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;


    User's image

    1 person found this answer helpful.

  2. NguyenM2 25 Reputation points

    Result will be:

    User's image

    User's image

    0 comments No comments

  3. Jingyang Li 5,891 Reputation points
    create table test (ID int, status varchar(8), type char(1))
    insert into test values
    ;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
    0 comments No comments

  4. Guoxiong 8,126 Reputation points

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