Suppose there is a sample of 99 records and it is necessary to break it into segments of a certain length, say 25.

There is a key in the sample.

Sample start data:

ID

one

2

3

four

five

After a split of 2 will be like this:

ID PAGE

eleven

2 1

3 2

4 2

5 3

This is easily done through the cursor. However, can this be done without a cursor in MS SQL 2008 r2?

  • one
    ntile(50) over (order by id) ? - teran 7:16 pm
  • @teran, hmm ... Gotta try. - iluxa1810 pm
  • or what thread dense_rank() over (order by rn/2) , where rn is the line number? - teran
  • @teran and what is rn? Is this the result of another window function? - iluxa1810 pm
  • @ iluxa1810 row_number - PashaPash ♦ 7:26 pm

1 answer 1

In order to summarize the comments, take a test sample of data from 100 consecutive numbers (we assume that they are not so consistent)

 WITH dataset AS ( select 1 as id union all select id+1 from dataset where id < 100 ) select * from dataset;

Further, from among the ranking functions, we can use ntile(N) , which divides the sample into N groups. That is, to split 100 lines of 2, it is necessary to divide into 50 groups. For the general case, this number can be defined by a subquery:

 SELECTid, ntile( (select count(id)/2 from dataset) ) over (order by id) FROM dataset;

and the result

 id ----------- -------------------- 1 1 2 1 3 2 4 2 5 3 6 3 7 4

Or the second option is to use the function dense_rank() , and for ranking, take the line numbers, dividing them completely into the desired number of elements in the group:

 ..... , nums AS ( SELECT id , (row_number() OVER (ORDER BY id) -1) AS rn FROM dataset ) SELECT id, dense_rank() OVER ( ORDER BY rn/2) FROM nums