Help with NTILE

• MissyDaisy

  Ten Centuries

  Points: 1164

  More actions

  November 22, 2009 at 7:26 am

  #139586

  I am using NTILE to split my result set into smaller chunks to pass to SAP BAPI which can only handle small sets of data.

  But it looks like I do not know how to use NTILE properly.

  Here is a sample where I would like to see 10 groups with NTILE(10) but the output is 7 groups????

  100011

  100021

  100032

  100042

  100053

  100063

  100074

  100085

  100096

  1000107

  Thank you much in advance.

  declare @SampleInput table

  ( CustomerID int )

  declare @SampleOutput table

  ( CustomerID int,

  GroupNum int )

  insert into @SampleInput

  select 10001

  insert into @SampleInput

  select 10002

  insert into @SampleInput

  select 10003

  insert into @SampleInput

  select 10004

  insert into @SampleInput

  select 10005

  insert into @SampleInput

  select 10006

  insert into @SampleInput

  select 10007

  insert into @SampleInput

  select 10008

  insert into @SampleInput

  select 10009

  insert into @SampleInput

  select 100010

  insert into @SampleInput

  select 100011

  insert into @SampleInput

  select 100012

  insert into @SampleInput

  select 100013

  INSERT into @SampleOutput

  select distinct top 10 customerid, NTILE(10) OVER(ORDER BY CustomerID ) from @SampleInput

  -- display output ( expected to see 10 groups each having 1 customerid

  select * from @SampleOutput

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  November 22, 2009 at 10:40 am

  #1082728

  MissyDaisy modify your T-SQL as:

  --Your T-SQL

  select distinct top 10 customerid, NTILE(10) OVER(ORDER BY CustomerID ) from @SampleInput

  --Modifed T-sQL

  select distinct top 10 customerid, NTILE(10) OVER(PARTITION BY customerID ORDER BY CustomerId DESC) from @SampleInput

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  November 22, 2009 at 1:12 pm

  #1082738

  The overlooked thing is the TOP operator.

  declare @SampleInput table

  ( CustomerID int )

  insert into @SampleInput

  select 10001

  insert into @SampleInput

  select 10002

  insert into @SampleInput

  select 10003

  insert into @SampleInput

  select 10004

  insert into @SampleInput

  select 10005

  insert into @SampleInput

  select 10006

  insert into @SampleInput

  select 10007

  insert into @SampleInput

  select 10008

  insert into @SampleInput

  select 10009

  insert into @SampleInput

  select 100010

  insert into @SampleInput

  select 100011

  insert into @SampleInput

  select 100012

  insert into @SampleInput

  select 100013

  --select top 10 * from (

  select customerid, NTILE(10) OVER (ORDER BY CustomerID ) as grp from @SampleInput

  --) AS d

  select top 10 * from (

  select customerid, NTILE(10) OVER (ORDER BY CustomerID ) as grp from @SampleInput

  ) AS d

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• MissyDaisy

  Ten Centuries

  Points: 1164

  More actions

  November 22, 2009 at 7:29 pm

  #1082784

  Thanks!