Home 2017 October SQL Puzzle: SQL Advance Query – Group and Count of Consecutive flags

SQL Puzzle: SQL Advance Query – Group and Count of Consecutive flags

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output for grouping and counting of consecutive flags data. For example, for id (1,2) = three 0, for id (4,5) = two 1.

Input data:

Expected Output:

Create a table with data:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Puzzle: SQL Advance Query - Find maximum consecutive wins for team IndiaSQL Puzzle: SQL Advance Query - Use STUFF() and form the aggregate of columns
Comments: 4
  1. Dinesh.IS
    October 23, 2017 at 10:41 am

    Declare @Result Table(ID Int,SLNo Int)
    Declare @ID As Int,@Val Int=”,@RowCnt Int,@PVal Int=”;
    Select @ID=Min(ID) From TBL_Flags
    Set @RowCnt=1;

    While @ID Is Not Null
    Begin
    Select @Val=Flag From TBL_Flags Where ID=@ID

    If @PVal@Val
    Begin
    Set @RowCnt=@RowCnt+1
    End

    Insert Into @Result
    (ID,SLNo)
    Values(@Val,@RowCnt)

    Select @ID=Min(ID) From TBL_Flags Where ID>@ID

    Set @PVal=@Val

    End

    Select ID As FLag,Count(SLNo) As NoOfCnt From @Result
    Group By ID,SLNo
    Order By ID,SLNo

    ReplyCancel
  2. Sql
    February 13, 2018 at 6:15 am

    why did u use id- ROW_NUMBER() OVER (PARTITION BY flag ORDER BY id) rnk ?
    Just for grouping? Thanks.

    ReplyCancel
  3. Jerry
    May 4, 2019 at 12:08 pm

    ;WITH CTE
    AS
    (
    SELECT id,flag,LAG(flag)OVER (ORDER BY id)lg,IIF(flag=(LAG(flag)OVER (ORDER BY id)),0,1)R
    FROM tbl_flags
    ),
    CTE2
    AS
    (
    SELECT id,flag,SUM(R)OVER(ORDER BY id)S
    FROM CTE
    )
    SELECT flag,COUNT(S)
    FROM CTE2
    Group By flag,S

    ReplyCancel

Leave a Reply to Anvesh Patel Cancel reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

October 19, 2017 4 Comments SQL Puzzle, , , , , , , , , ,