Praveen Kumar

Praveen Kumar

  • 335
  • 6.2k
  • 1.8m

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

Jun 29 2020 3:47 AM
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.
 
  1. Declare @maxRecord int = 5 --where I can use it?  
  2. Declare @Table Table (Id int, Location Int)  
  3.   
  4. insert into @Table Values(1,1)  
  5.                         ,(2,1)  
  6.                         ,(3,1)  
  7.                         ,(4,2)  
  8.                         ,(5,2)  
  9.                         ,(6,2)  
  10.                         ,(7,2)  
  11.                         ,(8,2)  
  12.                         ,(9,1)  
  13.                         ,(10,1)  
  14.                         ,(11,1)  
  15.                         ,(12,1)  
  16.   
  17. Select Id,Location,DENSE_RANK() Over(Order By Location) as GroupNo  
  18. From @Table  
  19. Order By GroupNo  
 

Brought to you by: JavaScript SDK for Bold BI dashboard and analytics embedding. Free trial.

Answers (1)