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;
GO
SET NOCOUNT ON
--Safety Check
IF OBJECT_ID('#recordDistributionTest','U') IS NOT NULL
   DROP TABLE #recordDistributionTest;
GO
--Create the test table
CREATE TABLE #recordDistributionTest
       (rowNum INT IDENTITY(1,1),
        numVal AS (rowNum * 10)
       );
GO
--Insert 17 records into the test table
INSERT INTO #recordDistributionTest
DEFAULT VALUES;
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;
GO

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.