#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;
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.

image

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.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s