How Do I ? Batching the data

• vignesh.ms

  Hall of Fame

  Points: 3456

  More actions

  July 6, 2015 at 12:18 am

  #319630

  Hi There,

  I need to group up the records randomly into ‘n’ number of batches. That can be done by NTILE, but I want group up similar records in single group.

  Say for example, following is the list of records I have in my table which I want to group into 5 batches

  A123

  A124

  A124

  A123

  A127

  After Ntile I will get the below,

  

  Desired output is, Need output like Ntile but all same id should reside in single batch

  

  Even if I n=5, maximum possibility of batches are 3 only.

  Thanks in advance

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  July 6, 2015 at 12:55 am

  #1810358

  So do a GROUP BY first and then break the results into groups. After that, 'explode' the results back into multiple rows if needed.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  July 6, 2015 at 1:25 am

  #1810361

  Something like this?

  SELECT x.id

  , DENSE_RANK() OVER (ORDER BY x.id) as DenseRnk

  FROM

  (SELECT 'A123' AS ID

  UNION ALL

  SELECT 'A124'

  UNION ALL

  SELECT 'A124'

  UNION ALL

  SELECT 'A123'

  UNION ALL

  SELECT 'A127') x

  And then do like Phil said and join back to the original table to show where to put each item?