Sort query by row count

[jj_dahlheimer]

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.

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

Results
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

[B.Hauser]

Try to modify your query as follows:

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[B] [COLOR=#c0392b]10.0[/COLOR][/B] Else [B][COLOR=#c0392b]9.0[/COLOR] [/B]End
From Picksort
Order By Row_Number() Over (Partition By Bucket Order By Bucket) / Case When Bucket = 1 Then [COLOR=#c0392b][B]10.0[/B][/COLOR] Else [COLOR=#c0392b][B]9.0[/B][/COLOR] End

Row_Number returns an Integer Value and 10 and 9 are also interpreted as integer values.
The rule is: If you calculate only with integers, the result will be an integer again, i.e. decimal positions are truncated.
10.0 and 9.0 are interpreted as decimals, so internally is now calculated with FLOAT ... and decimal positions are returned.

Birgitta

[jj_dahlheimer]

I did try that and it only works this way because it truncates it. Otherwise is just sorts all the top row counts from each bucket to the top.

I also don't like my solution as I am unsure what forces bucket 1 to come first, is it the way the partition is done or just so happens the SQE returned it the same way it is in the file.

Here are the results with the decimals.

8180475	1	1	0.10000000000
8180491	2	1	0.11111111111
8180474	3	1	0.11111111111
8180489	4	1	0.11111111111
8180487	5	1	0.11111111111
8180579	6	1	0.11111111111
8180569	7	1	0.11111111111
8180476	1	2	0.20000000000

[B.Hauser]

Not sure if I understood your problem correctly, but ... will the following solution return what you want?

With x as (Select Pick, Bucket,
                  Case When Mod(Bucket, 2,0) > 0 Then Row_Number() Over(Partition By Bucket Order By Pick) Else 0 End Nbr1,
                  Case When Mod(Bucket, 2,0) = 0 Then Row_Number() Over(Partition By Bucket Order By Pick) Else 0 End Nbr2
             from testx)
Select x.*, Nbr1/10 Sort1, Nbr2/9 Sort2
   from x
   Order By Case When Sort1 > 0 Then Sort1 else Sort2 End,
          Bucket, Pick; 

Birgitta

[Vectorspace]

I was a bit confused by your comment that you want 10 from bucket 1 and 9 from bucket 2. But I have assumed if there were more buckets this would continue in the same fashion i.e. 8 from bucket #3, 7 form #4, etc. In which case this pickCountPerBucket can be computed as 11 minus bucket number. Eg. bucket 1, 11-1 = 10. Bucket 2, 11-2 = 9.

You are already giving a row number to each item within each bucket.

select f.*,
       row_number() over(partition by bucket order by pick#) as indexInBucket
  from dataset f

If we take this row number and subtract 1 (so they are numbered 0, 1, 2, etc,) and divide by the pickCountPerBucket, then e.g. for bucket 1 the first 10 items will be 0, the next 10 will be 1, etc. Add 1 to have the first 10 be 1, the second 10 be 2, etc. I call this picksetInBucket, and it can be calculated as follows

  ((indexInBucket - 1) / max((11-bucket),1)) + 1 as picksetInBucket

(indexInBucket - 1): convert the in-bucket ordering from 1 to n, to 0 to n-1
(11-bucket) - compute the number of picks at a time from the bucket nummer
* max((11-bucket),1) ensure it can never be less than 1
divide the former from the latter, and add 1, to get the pickCountPerBucket (first 10 entries in bucket 1 = 2, next 10 = 2, etc. first 9 in bucket 2 = 1, next 9 = 2, etc)

So to avoid duplicating the row_number() statements, I will use a CTE for the first part, so the second part can reference the rowe-number() value

with addIndex as (
  select f.*,row_number() over(partition by bucket order by pick#) as indexInBucket
    from dataset f)

select pick#, bucket, indexInBucket,
       ((indexInBucket - 1) / max((11-bucket),1)) + 1 as picksetInBucket
  from addIndex f
 order by 4, 2;

So the addIndex CTE adds the pick item index within bucket, so the subsequent query canr eference the CTE like it's a table and compute the pick order.

"order by 4, 2"
integer literals in the order by clause are interpreted as column numbers. So this means order by column 4 (picksetInBucket) and then column 2 (bucket number). Note this only works with hard coded liteals. Not scalar functions, not host variables, nothing else.

[jj_dahlheimer]

Birgitta - Statement did similar as mine, only sorted 9 rows to the top the first bucket.

Vector - Thanks for taking the time to try that but it did not get me what I needed either. It grabbed one from each bucket not the 10 from bucket 1.

I ended up writing a program and using some arrays to create the sort I needed. Not a clean solution but I think the sql would have been equally complicated. Operations also wants be able to control the X per bucket number as well.

To give a final recap just to explain. This related to an automated picking line, boxes going down a conveyor and getting diverted to different areas of the warehouse (buckets). If there are to many boxes in a row that go to one area then the line backs up. So by ordering the boxes by X number for each area it prevents the backup. Think cars on a single lane highway with exits along the way, if to many cars in a row try and take the same exit they will eventually back up onto the highway.

Thanks for the help.

[Vectorspace]

Mine worked for me. I created a test dataset that had 8 buckets, with 46 pick# entries in each bucket. My SQL returned 10 from bucket #1, then 9 from #2, then ... then 3 from #8, then the next 10 from #1, etc.

Here is the full SQL unscluding the test data set

with dataset(pick#,bucket) as (values
(5910,6),(7368,8),(11235,8),(12397,6),(18202,4),(19025,5),(22815,6),
(23722,5),(26085,8),(27321,2),(28454,6),(29240,2),(30860,7),(31090,8),
(42554,5),(46051,4),(47337,1),(50192,5),(51570,6),(53236,4),(55314,6),
(57356,1),(58024,6),(63048,2),(63982,1),(73452,3),(78496,3),(79420,6),
(80489,7),(83810,5),(84863,3),(89876,8),(92365,8),(93340,4),(97687,2),
(100862,4),(108069,6),(109129,3),(115325,2),(115795,3),(117158,7),
(119631,2),(123017,8),(123782,1),(125668,1),(129753,8),(130031,4),
(132653,2),(132808,2),(132913,3),(133060,8),(134202,5),(134891,1),
(135936,4),(141043,7),(141919,7),(143628,6),(145944,5),(148631,3),
(149929,1),(157891,2),(158380,5),(159446,4),(161044,7),(161550,5),
(164729,4),(167738,7),(168934,5),(169566,4),(175611,1),(185793,4),
(187537,4),(188648,4),(198475,6),(198671,3),(202734,8),(203881,8),
(205529,8),(206082,5),(216645,3),(220143,4),(221325,1),(224038,7),
(224418,5),(231487,2),(231576,7),(232531,5),(236928,1),(245631,4),
(247775,6),(249440,8),(254929,7),(256217,2),(258082,1),(264950,7),
(270677,2),(273178,4),(275026,7),(276411,4),(277840,3),(287184,3),
(289199,8),(290154,4),(290820,5),(291341,6),(296007,3),(297281,7),
(297361,2),(300353,7),(303768,4),(303931,1),(304892,3),(311155,1),
(312961,6),(318088,7),(320811,1),(322703,2),(322713,6),(324946,1),
(327392,2),(328774,8),(328968,6),(330765,4),(334881,6),(335504,2),
(336262,2),(336945,3),(337885,8),(341364,8),(355938,6),(357188,2),
(358144,5),(358650,6),(358770,3),(361109,2),(373174,1),(379774,4),
(381094,5),(385274,2),(386325,3),(386823,4),(390550,7),(393928,2),
(394089,5),(396805,4),(397042,3),(398610,1),(400633,3),(405482,6),
(409953,2),(413833,8),(416303,1),(416875,5),(418925,3),(421811,8),
(423309,6),(424835,5),(424880,1),(430059,2),(432516,6),(436791,8),
(444517,4),(445081,3),(449099,7),(451689,1),(452033,6),(455282,6),
(458035,3),(460501,8),(460776,1),(464144,5),(464588,1),(468735,7),
(471651,5),(477720,1),(482936,6),(488382,5),(503166,5),(505301,5),
(515064,2),(515877,2),(520214,1),(522118,2),(522313,3),(522502,6),
(523792,5),(526156,5),(526307,2),(532574,8),(540281,3),(544301,5),
(546576,6),(550024,7),(556878,1),(559280,7),(559781,7),(561696,6),
(566561,7),(567628,6),(570539,1),(574514,7),(574759,4),(576417,3),
(580121,6),(584064,5),(588364,1),(589221,3),(592659,4),(594111,2),
(599923,2),(603461,6),(604636,2),(606140,5),(606167,8),(607113,3),
(615258,7),(615858,8),(618323,7),(619724,3),(621844,8),(622214,7),
(622661,1),(623519,2),(627480,7),(631199,2),(633242,7),(635350,3),
(636671,1),(639524,6),(640827,2),(640903,7),(643443,6),(647324,3),
(648239,7),(649720,1),(649959,6),(652697,7),(657848,8),(658560,1),
(659399,1),(660636,5),(662143,7),(662413,5),(663824,8),(664320,4),
(664891,5),(676167,5),(677562,8),(682208,5),(687828,8),(688464,6),
(692623,4),(696610,8),(697895,3),(704924,4),(705069,1),(706408,2),
(708449,8),(713115,1),(717333,4),(718360,3),(719586,8),(721661,8),
(722009,8),(727763,7),(734162,4),(737755,5),(756929,2),(758917,6),
(761007,2),(763395,4),(763598,2),(763902,3),(765572,7),(772395,1),
(772823,3),(777781,2),(783085,1),(786839,7),(788110,7),(789280,4),
(793657,8),(794625,6),(798174,5),(799470,3),(799637,7),(807884,3),
(811910,8),(812699,5),(817462,7),(817885,4),(822004,6),(822539,1),
(824018,3),(824785,6),(826241,8),(829548,3),(830022,3),(833892,8),
(835535,8),(840429,4),(840586,7),(842615,7),(844509,2),(845852,8),
(846284,1),(846387,4),(847322,8),(847608,8),(853148,3),(855721,2),
(860350,6),(863028,6),(867389,3),(871033,6),(874139,5),(876484,1),
(877747,6),(879005,3),(879399,3),(883234,7),(888378,7),(890637,1),
(891838,7),(896746,1),(911689,3),(914925,7),(915429,4),(915947,1),
(918750,4),(920691,5),(922660,2),(924342,5),(924422,4),(926650,4),
(927077,1),(929206,8),(931776,8),(933751,5),(935208,8),(937308,5),
(941342,4),(945810,5),(948717,3),(950262,6),(952589,4),(963118,6),
(964503,1),(967008,1),(968219,7),(969468,2),(971319,4),(971796,6),
(977953,5),(980763,5),(983109,2),(986323,5),(991320,4),(994184,3),
(995821,2),(995923,2),(996870,1),(996871,2),(996872,8),(996873,4),
(996874,4),(996875,3),(996876,7)
),

/*with*/
addIndex as (
  select f.*,row_number() over(partition by bucket order by pick#) as indexInBucket
    from dataset f)

select pick#, bucket, indexInBucket,
       ((indexInBucket - 1) / max((11-bucket),1)) + 1 as picksetInBucket
  from addIndex f
 order by 4, 2;

[jj_dahlheimer]

Interesting, when using my table it is casting picksetinbucket as a float which is what causes it not to sort correctly. I wrapped it with integer and it worked. My column is defined as decimal(1,0) so I am guessing that is what is causing it.

Also modified a little so that I can make the X dynamic based on what operations wants it set to.

with addIndex as (
select f.*,row_number() over(partition by bucket order by pick#) as indexInBucket
from picksort f)

select pick#, bucket, indexInBucket,
integer(((indexInBucket - 1) / max((
case when bucket = 1 then 10
when bucket = 2 then 10
when bucket = 3 then 10
when bucket = 4 then 5
when bucket = 5 then 5
when bucket = 6 then 4
when bucket = 7 then 4
else 2 end),1)) + 1) as picksetInBucket
from addIndex f
order by 4, 2;

[Vectorspace]

Glad to hear it's working!

You could probably modify your case statement to simplfy it a bit:

case when bucket <= 3 then 10
when bucket <= 5 then 5
when bucket <= 7 then 4
else 2 end

[Vectorspace]

"&lt;" should be "<" obviously