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: 246985

  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

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• pietlinden

  SSC Guru

  Points: 63509

  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?