How to group records where each group can have maximum 5 records

In SQL Server, I have to group records by a column where each group can have at most 5 records

To achieve it, I am using the following query which groups my data into two groups. Where the first group have more than 5 records. Kindly suggest me, how to set the limit.

Declare @maxRecord int = 5 --where I can use it?
Declare @Table Table (Id int, Location Int)
insert into @Table Values(1,1)
,(2,1)
,(3,1)
,(4,2)
,(5,2)
,(6,2)
,(7,2)
,(8,2)
,(9,1)
,(10,1)
,(11,1)
,(12,1)
Select Id,Location,DENSE_RANK() Over(Order By Location) as GroupNo
From @Table
Order By GroupNo

Try this:

Declare @maxRecord int = 5 --where I can use it?
Declare @Table Table (Id int, Location Int)
insert into @Table Values(1,1)
,(2,1)
,(3,1)
,(4,2)
,(5,2)
,(6,2)
,(7,2)
,(8,2)
,(9,1)
,(10,1)
,(11,1)
,(12,1)
,(13,3)
,(14,2)
,(15,1)
,(16,1)
,(17,2)
,(18,1)
,(19,1)
,(20,1)
SELECT
Id
,Location
,DENSE_RANK() over (order by [Group], [DivByVariable])
FROM(
Select
Id
, Location
, (row_number() over (partition by Location order by Id)-1)/@maxRecord [DivByVariable]
, DENSE_RANK() over (order by Location) [Group]
From @Table
)A

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Kamil Krupski6y ago
Accepted answer
Try this:
Declare @maxRecord int = 5 --where I can use it?
Declare @Table Table (Id int, Location Int)

insert into @Table Values(1,1)
                        ,(2,1)
                        ,(3,1)
                        ,(4,2)
                        ,(5,2)
                        ,(6,2)
                        ,(7,2)
                        ,(8,2)
                        ,(9,1)
                        ,(10,1)
                        ,(11,1)
                        ,(12,1)
                        ,(13,3)
                        ,(14,2)
                        ,(15,1)
                        ,(16,1)
                        ,(17,2)
                        ,(18,1)
                        ,(19,1)
                        ,(20,1)

SELECT
    Id
    ,Location
    ,DENSE_RANK() over (order by [Group], [DivByVariable])
FROM(
Select
    Id
    , Location
    , (row_number() over (partition by Location order by Id)-1)/@maxRecord [DivByVariable]
    , DENSE_RANK() over (order by Location) [Group]
From @Table
)A
+1