Select alternate rows basis on pattern.

Question

I have a table like below

I want the result like below

Please help on this.

Thanks in advance.

Answer 1

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".

Answer 2

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