QUERY HELP

• Barcelona10

  SSCertifiable

  Points: 5372

  More actions

  July 17, 2023 at 11:45 pm

  #4237649

  Hi all

  I have the following table

  DECLARE @T table (A int )

  insert into @T

  values

  (1)

  ,(0)

  ,(1)

  ,(0)

  ,(1)

  ,(2)

  ,(1)

  ,(0)

  ,(1)

  ,(2)

  ,(3)

  ,(2)

  ,(0)

  ,(1)

  The results for B column should look like

  

   

  Which means every time after  A=0  it's a start for new group ranking .

  Thank You advance

  • This topic was modified 1 year, 4 months ago by  Barcelona10.

• Barcelona10

  SSCertifiable

  Points: 5372

  More actions

  July 17, 2023 at 11:47 pm

  #4237650

  Sorry

  DECLARE @T table (A int )

  insert into @T

  values

  (1)

  ,(0)

  ,(1)

  ,(0)

  ,(1)

  ,(2)

  ,(1)

  ,(0)

  ,(1)

  ,(2)

  ,(3)

  ,(2)

  ,(0)

  ,(1)

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  July 18, 2023 at 1:28 am

  #4237697

  There is nothing to order the table by. Maybe you should add an identity column to your table first.

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  July 18, 2023 at 11:14 am

  #4238119

  I've had to add an identity column (ID) so the table can be ordered by something:

  DECLARE @T table (ID int IDENTITY(1, 1),  A int )
  
  insert 
  into @T(A)
  values (1),(0),(1),(0),(1),(2),(1),(0),(1),(2),(3),(2),(0),(1)
  ;
  
  
  ;WITH CTE AS
  (
      SELECT ID, A,
             CASE WHEN LAG(A) OVER(ORDER BY ID) = 0 THEN 1 ELSE 0 END IsPrevZero
        FROM @T
  )
  SELECT A,
        SUM(IsPrevZero) OVER (ORDER BY ID) + 1 B
    FROM CTE
    ORDER BY ID
  ;
  

• drew.allen

  SSC Guru

  Points: 76989

  More actions

  July 18, 2023 at 1:14 pm

  #4238224

  Jonathan AC Roberts wrote:

  I've had to ad an identity column (ID) so the table can be ordered by something:

  DECLARE @T table (ID int IDENTITY(1, 1),  A int )
  
  insert 
  into @T(A)
  values (1),(0),(1),(0),(1),(2),(1),(0),(1),(2),(3),(2),(0),(1)
  ;
  
  
  ;WITH CTE AS
  (
      SELECT ID, A,
             CASE WHEN LAG(A) OVER(ORDER BY ID) = 0 THEN 1 ELSE 0 END IsPrevZero
        FROM @T
  )
  SELECT A,
        SUM(IsPrevZero) OVER (ORDER BY ID) + 1 B
    FROM CTE
    ORDER BY ID
  ;
  

  I would make a couple of changes to Jonathan's code:

  • Supply the optional 2nd and 3rd parameters for the LAG() function.

    • This removes the fencepost error.
    • Removing the fencepost error, allows you to simplify the Column B calculation.

  • I recommend that you ALWAYS specify the frame.

  Here is the updated code:

  DECLARE @T table (ID int IDENTITY(1, 1),  A int )
  
  insert 
  into @T(A)
  values (1),(0),(1),(0),(1),(2),(1),(0),(1),(2),(3),(2),(0),(1)
  ;
  
  
  ;WITH CTE AS
  (
      SELECT ID, A,
             CASE WHEN LAG(A,1,0) OVER(ORDER BY ID) = 0 THEN 1 ELSE 0 END IsPrevZero
        FROM @T
  )
  SELECT A,
        SUM(IsPrevZero) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) B  -- removed the + 1
    FROM CTE
    ORDER BY ID
  ;
  

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• drew.allen

  SSC Guru

  Points: 76989

  More actions

  July 18, 2023 at 1:23 pm

  #4238227

  Here is an alternate solution that doesn't require a CTE.  NOTE: I've used COUNT() instead of SUM(), because COUNT() never returns a NULL value, whereas SUM() might--and does for the first two records.

  SELECT A, COUNT(CASE WHEN A = 0 THEN 1 ELSE NULL END) OVER(ORDER BY t.ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) + 1 AS B
  FROM @T AS t;
  

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Barcelona10

  SSCertifiable

  Points: 5372

  More actions

  July 18, 2023 at 3:59 pm

  #4238362

  Thank You ALL very much!