Select alternate rows basis on pattern

sourav dutta 231 Reputation points
2024-07-01T12:00:38.2466667+00:00

I have a table like below

User's image

I want the result like below

User's image

Please help on this.

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,265 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
{count} votes

Accepted answer
  1. LiHongMSFT-4306 25,571 Reputation points
    2024-07-02T02:36:55.1133333+00:00

    Hi @sourav dutta

    The above query is not working. I am using SQL SERVER 2019 (Web edition)

    The predicates IS DISTINCT FROM used in Erland's code applies to SQL Server 2022 (16.x).

    See: IS [NOT] DISTINCT FROM (Transact-SQL)

    A IS DISTINCT FROM B will decode to: ((A <> B OR A IS NULL OR B IS NULL) AND NOT (A IS NULL AND B IS NULL))

    So, try the modified query below:

    ;WITH CTE AS 
    (
     SELECT *, LAG(IsActive) OVER(ORDER BY Id) AS PrevIsActive
     FROM tbl
    )
    SELECT Id, FirstName,CurrentTimeStamp, IsActive
    FROM   CTE
    WHERE ((IsActive <> PrevIsActive OR IsActive IS NULL OR PrevIsActive IS NULL) AND NOT (IsActive IS NULL AND PrevIsActive IS NULL))
    

    Or

    ;WITH CTE AS 
    (
     SELECT *,LAG(IsActive,1,2/*custom value that not exists in(0,1)*/) OVER(ORDER BY Id) AS PrevIsActive
     FROM tbl
    )
    SELECT Id, FirstName,CurrentTimeStamp, IsActive
    FROM   CTE
    WHERE IsActive <> PrevIsActive 
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 105.9K Reputation points MVP
    2024-07-01T12:46:38.5666667+00:00

    There are three problems with your question:

    1. You give the sample data as an image. If you provide the data as CREATE TABLE + INSERT, we can copy and paste into a query window to develop a tested solution.
    2. Your post does not include a verbal description of your business rules.
    3. You don't say which version of SQL Server you are using.

    The net effect of these flaws is that what follows is a guess that I have not tested. The query requires SQL 2022.

    ; WITH CTE AS (
        SELECT *, LAG(IsActive) OVER(ORDER BY Id) AS PrevIsActive
        FROM   tbl
    )
    SELECT Id, FirstName, CurrentTimeStamp, IsActive
    FROM   CTE
    WHERE  IsActive IS DISTINCT FROM PrevIsActive