#0345 – SQL Server – Distributing a result set by equal number of rows

Recently, a colleague brought an interesting query to me – the design for one of their modules required that records in a table be grouped into chunks of a specified integer value and each record within this group be assigned a number starting from 1. Hence, if I had 20 records in a table and the group size was 5 records, I would end up with 4 groups and the records would need to be numbered from 1-5 within each group.

Note here that I do not want a fixed number of groups (I can easily achieve this using NTILE). The groups can reach any number – what I need is a fixed number of elements within a group.

This post documents the first solution that came to my mind. While that particular deliverable is complete by the time I write this post, I look forward to hearing about other solutions/suggestions to achieve this requirement.

Creating the Test Scenario

To create a test scenario, I first create a simple test table and populate it with some test data. To make things interesting, I have inserted a prime number of records.

USE tempdb;
--Safety Check
IF OBJECT_ID('#recordDistributionTest','U') IS NOT NULL
   DROP TABLE #recordDistributionTest;
--Create the test table
CREATE TABLE #recordDistributionTest
       (rowNum INT IDENTITY(1,1),
        numVal AS (rowNum * 10)
--Insert 17 records into the test table
INSERT INTO #recordDistributionTest
GO 17

The solution

The solution that first came to my mind was to use the modulo operator (%) on the identity/row number value.

For the purposes of this example, I have used a @recordsInAGrop variable which is set to 5.

--Variable to hold the number of records in a group
DECLARE @recordsInAGroup INT = 5;
--Perform the grouping when fetching the records
SELECT rdt.rowNum AS RowNumber,
       rdt.numVal AS RecordValue,
       CASE WHEN (rdt.rowNum % @recordsInAGroup) = 0
            THEN @recordsInAGroup
            ELSE (rdt.rowNum % @recordsInAGroup)
       END AS RowNumberWithinGroup
FROM #recordDistributionTest AS rdt;

Following is the screenshot of the result obtained from this method.


As can be seen from the result, the first 15 records were grouped into 3 groups of 5 each. The last 2 records don’t belong to any group of the size specified in @recordsInAGroup variable and are therefore easily identifiable for further processing.

Do you have any suggestions on how to achieve this requirement in a much more efficient manner? Do drop in a line with your thoughts/suggestions.

Until we meet next time,

Be courteous. Drive responsibly.


Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.