ntile or something similar

• matak

  SSCrazy

  Points: 2674

  More actions

  February 24, 2013 at 9:07 pm

  #274536

  Hi,

  Im trying to group the below sample data into 4 equal parts and cant quite get it right.

  Hoping someone can help.

  Setup data

  IF OBJECT_ID('TempDB..#stress','U') IS NOT NULL

  DROP TABLE #stress

  CREATE TABLE #stress

  (

  stress float,

  )

  insert into #stress

  select 4.62808055565656 union all

  select 5.03749736191992 union all

  select 5.37706547116375 union all

  select 6.75654161261666 union all

  select 6.75654161261666 union all

  select 6.93216131688872 union all

  select 6.93216131688872 union all

  select 7.20953531826428 union all

  select 7.24910670229343 union all

  select 7.58537222592524 union all

  select 7.58537222592524 union all

  select 7.58537222592524 union all

  select 7.58537222592524 union all

  select 7.58537222592524 union all

  select 7.58537222592524 union all

  select 7.58537222592524 union all

  select 7.58537222592524 union all

  select 17.3024897408782 union all

  select 17.3024897408842 union all

  select 17.4410386471244

  What i thought was going to work was

  select

  ntile(4) over (order by stress asc),

  stress

  from #stress

  Unfortunately i misread what ntile did and its not giving me the resultset im after.

  Here is the output im actually after.

  Instead of splitting the results into 4 equal chunks from top to bottom (ntile i believe) I want to assign each "container" the lowest numbers possible.

  So ordering from lowest to highest i want the 1st number in container1, 2nd container2, 3rd container 3, 4th container4, 5 container1, 6th container2 etc

  Im hoping it makes sense and will do my best to explain better if needed.

  select 1, 4.62808055565656 union all

  select 2, 5.03749736191992 union all

  select 3, 5.37706547116375 union all

  select 4, 6.75654161261666 union all

  select 1, 6.75654161261666 union all

  select 2, 6.93216131688872 union all

  select 3, 6.93216131688872 union all

  select 4, 7.20953531826428 union all

  select 1, 7.24910670229343 union all

  select 2, 7.58537222592524 union all

  select 3, 7.58537222592524 union all

  select 4, 7.58537222592524 union all

  select 1, 7.58537222592524 union all

  select 2, 7.58537222592524 union all

  select 3, 7.58537222592524 union all

  select 4, 7.58537222592524 union all

  select 1, 7.58537222592524 union all

  select 2, 17.3024897408782 union all

  select 3, 17.3024897408842 union all

  select 4, 17.4410386471244

• zzalina0

  SSCommitted

  Points: 1524

  More actions

  February 24, 2013 at 10:43 pm

  #1590695

  Hi )

  I propose you to try this solution:

  1. Number all rows in asc order

  2. Select stress value and (row_number mod 4)

  select s.stress,

  case when(row_number %4)=0 then 4 else row_number %4 end

  from

  (select stress, ROW_NUMBER() over (order by stress asc) row_number from #stress) s

• matak

  SSCrazy

  Points: 2674

  More actions

  February 24, 2013 at 10:47 pm

  #1590698

  Excellent - looks like this will do the trick.

  Thanks for the help.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 25, 2013 at 4:31 am

  #1590738

  SELECT *

  FROM (

  SELECT *,

  Chunk = 1+(ROW_NUMBER() OVER(ORDER BY stress)-1)%4

  FROM #stress

  ) d

  ORDER BY Chunk, Stress

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• matak

  SSCrazy

  Points: 2674

  More actions

  February 25, 2013 at 6:33 pm

  #1590993

  Thanks Chris,

  This looks like it will be a bit easier to use in my actual code.