Using Ntile for fixed number of rows

• koti.raavi

  SSCrazy

  Points: 2756

  More actions

  June 21, 2017 at 5:02 am

  #331316

  Hi All,

  I have 3200 records in table and want to segregate data as 600 rows as one batch, So first 5 bathes Id's 1-5 (600*5=3000) and group 6 should be 200records, thanks for help.

• Eirikur Eiriksson

  SSC Guru

  Points: 182920

  More actions

  June 21, 2017 at 5:29 am

  #1947524

  koti.raavi - Wednesday, June 21, 2017 5:02 AM

  Hi All,

  I have 3200 records in table and want to segregate data as 600 rows as one batch, So first 5 bathes Id's 1-5 (600*5=3000) and group 6 should be 200records, thanks for help.

  Can you post the DDL (create table) script and tell us a little more about the data (unique keys, desired order etc.)?
  😎

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  June 21, 2017 at 1:42 pm

  #1947629

  See if something like the following will work for you.

  Test data...
  
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;

  CREATE TABLE #TestData (
    ID INT NOT NULL,
    SomeTextString VARCHAR(50) NOT NULL
    );

  WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
    cte_Tally (n, v) AS (
        SELECT TOP 3200
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
            NEWID()
        FROM
            cte_n3 a CROSS JOIN cte_n3 b
        )
INSERT #TestData (ID, SomeTextString)
SELECT
    t.n, t.v
FROM
    cte_Tally t;

  -- SELECT * FROM #TestData td;


  The solution...
  
DECLARE @RowCount INT = (SELECT COUNT(*) FROM #TestData td);

  WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
    cte_Group (n) AS (
        SELECT 1
        UNION ALL
        SELECT TOP (@RowCount / 600)
            (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 600) + 1
        FROM
            cte_n3 a CROSS JOIN cte_n3 b
        )

  SELECT
    td.ID,
    td.SomeTextString,
    GroupNum = MAX(g.n) OVER (ORDER BY td.ID ROWS UNBOUNDED PRECEDING)
FROM
    #TestData td
    LEFT JOIN cte_Group g
        ON td.ID = g.n
ORDER BY
    td.ID


• Jeff Moden

  SSC Guru

  Points: 1004441

  More actions

  June 21, 2017 at 2:44 pm

  #1947640

  Jason A. Long - Wednesday, June 21, 2017 1:42 PM

  See if something like the following will work for you.

  Test data...
  
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;

  CREATE TABLE #TestData (
    ID INT NOT NULL,
    SomeTextString VARCHAR(50) NOT NULL
    );

  WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
    cte_Tally (n, v) AS (
        SELECT TOP 3200
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
            NEWID()
        FROM
            cte_n3 a CROSS JOIN cte_n3 b
        )
INSERT #TestData (ID, SomeTextString)
SELECT
    t.n, t.v
FROM
    cte_Tally t;

  -- SELECT * FROM #TestData td;


  The solution...
  
DECLARE @RowCount INT = (SELECT COUNT(*) FROM #TestData td);

  WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
    cte_Group (n) AS (
        SELECT 1
        UNION ALL
        SELECT TOP (@RowCount / 600)
            (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 600) + 1
        FROM
            cte_n3 a CROSS JOIN cte_n3 b
        )

  SELECT
    td.ID,
    td.SomeTextString,
    GroupNum = MAX(g.n) OVER (ORDER BY td.ID ROWS UNBOUNDED PRECEDING)
FROM
    #TestData td
    LEFT JOIN cte_Group g
        ON td.ID = g.n
ORDER BY
    td.ID


  Perhaps I'm under-engineering things a bit but these seems to do what is needed against your test data...
  
 SELECT BatchGroup = (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)/600+1
        ,*
   FROM #TestData
;


  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  June 21, 2017 at 2:59 pm

  #1947645

  Jeff Moden - Wednesday, June 21, 2017 2:44 PM

  Perhaps I'm under-engineering things a bit but these seems to do what is needed against your test data...
  
 SELECT BatchGroup = (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)/600+1
        ,*
   FROM #TestData
;


  Not at all... More like me over complicating it... Your solution is far better.
  Sometimes the 1st idea gets stuck in the ole noggin and blocks out an obviously better solution.
  That said, I'm don't know that I'd have even considered dividing by 600 to take advantage of the INT math on my own... Guess I shoulda had a V-8. :Whistling:
  Very nice code sir!

• Jeff Moden

  SSC Guru

  Points: 1004441

  More actions

  June 22, 2017 at 7:41 am

  #1947757

  Jason A. Long - Wednesday, June 21, 2017 2:59 PM

  Jeff Moden - Wednesday, June 21, 2017 2:44 PM

  Perhaps I'm under-engineering things a bit but these seems to do what is needed against your test data...
  
 SELECT BatchGroup = (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)/600+1
        ,*
   FROM #TestData
;


  Not at all... More like me over complicating it... Your solution is far better.
  Sometimes the 1st idea gets stuck in the ole noggin and blocks out an obviously better solution.
  That said, I'm don't know that I'd have even considered dividing by 600 to take advantage of the INT math on my own... Guess I shoulda had a V-8. :Whistling:
  Very nice code sir!

  Thanks for the feedback, Jason.  I appreciate it.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)