Have an interesting request by our operations that I have not been able to solve with just sql. We have a pick #, each pick # falls into a bucket, and there are 8 buckets. Our operations wants to sort the pick numbers by bucket and then by X number of rows per bucket. Once we hit the last bucket start all over and select the next X rows from bucket 1.
So for simplicity sake pretend we have 100 picks, 2 buckets with 50 picks in each bucket. I want to grab the first 10 picks from bucket 1, then 9 picks from bucket 2, then 10 picks from bucket 1....
This is as close as I could get, the problem is the first round the math does not work out. When I hit the 10th row the expression goes from 0 to 1 pushing it to the next group so I get one less row than I want.
Results
pick #, bucket, row number, expression
So for simplicity sake pretend we have 100 picks, 2 buckets with 50 picks in each bucket. I want to grab the first 10 picks from bucket 1, then 9 picks from bucket 2, then 10 picks from bucket 1....
This is as close as I could get, the problem is the first round the math does not work out. When I hit the 10th row the expression goes from 0 to 1 pushing it to the next group so I get one less row than I want.
Code:
SELECT pick#, bucket, ROW_NUMBER() OVER ( PARTITION BY bucket ORDER BY bucket), ROW_NUMBER() OVER ( PARTITION BY bucket ORDER BY bucket )/case when bucket = 1 then 10 else 9 end FROM picksort order by ROW_NUMBER() OVER ( PARTITION BY bucket ORDER BY bucket )/case when bucket = 1 then 10 else 9 end
pick #, bucket, row number, expression
8180475 | 1 | 1 | 0 |
8180476 | 1 | 2 | 0 |
8180477 | 1 | 3 | 0 |
8180478 | 1 | 4 | 0 |
8180479 | 1 | 5 | 0 |
8180494 | 1 | 6 | 0 |
8180512 | 1 | 7 | 0 |
8180526 | 1 | 8 | 0 |
8180534 | 1 | 9 | 0 |
8180491 | 2 | 1 | 0 |
8180495 | 2 | 2 | 0 |
8180498 | 2 | 3 | 0 |
8180505 | 2 | 4 | 0 |
8180507 | 2 | 5 | 0 |
8180508 | 2 | 6 | 0 |
8180510 | 2 | 7 | 0 |
8180514 | 2 | 8 | 0 |
Comment