SQL SELECT incremental batch number every X rows

问题:

I'm trying to write a SQL SELECT query that has a batch number column that increments every 5 rows, I've tried using a hacky integer division on a dynamic row number, but can't get around the first batch number increment always being 4 rows instead of 5 (see image).

RowNumber 1-5 should be 1, 6-10 should be 2 etc..

Any ideas how I can achieve this?

select * ROW_NUMBER() OVER (Order by (select 0)) AS RowNumber,
1 + convert(int, (ROW_NUMBER() OVER (Order by (select 0)) / 5)) as BatchNumber,
* from WorkQueue

回答1:

This should work:

select ROW_NUMBER() OVER (Order by (select 0)) AS RowNumber,
(case when convert(int, (ROW_NUMBER() OVER (Order by (select 0)) % 5))=0 then 0 else 1 end) + convert(int, (ROW_NUMBER() OVER (Order by (select 0)) / 5)) as BatchNumber,
* from WorkQueue

回答2:

Try this:

SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowNumber,
        CEILING(CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS DECIMAL(10,2))/5) AS BatchNo, *
FROM    WorkQueue