Kristaps Porzingis Kristaps Porzingis - 7 months ago 20
SQL Question

SQL Update Segment of 5 Records

I have a table of names and I want to generate another column "Group" that increases by 1 every 5 records. Below is an example of the desired output.

Name Group
Joe 1
Frank 1
Susan 1
Tom 1
Kim 1
Mike 2
John 2
Henry 2
Rick 2
Quinn 2

Answer

Creating a CTE with row number will help

;WITH cte AS
(
  SELECT 
    Name,
    ROW_NUMBER() OVER (ORDER BY Name) AS RowNum
  FROM YourTable
)

SELECT
  Name,
  (RowNum - 1) / 5 + 1 AS [Group]
FROM cte