CODEWITHSUNDEEP
sqlsql-servert-sql
wildshark uk
wildshark uk

Reputation: 13

T-SQL reset sequence every x records for group

I'm looking to create a dataset to categorise customers into specific groups for each customer type - I want to apply a numbering system to start at 1 for each customer type and group every 3 records for the customer type

In the sample below, I can see that the AUTO Customer type has 4 groups (3 x 3 & 1 x 2), BUIL has 1 group of 3, MANU has 3 groups (2 x 3 & 1 x 1) etc.

Sample

I was originally looking at the NTILE, but my groups have differing row counts per customer type so cannot dynamically alter the NTILE value AFAIK.

Any help would be greatly appreciated.

Thanks in advance

wildshark-uk

Upvotes: 1

Views: 208

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269993

Assuming you have a column that specifies the ordering, you can just use row_number() and arithmetic:

select t.*,
       ceiling(row_number() over (partition by customer_type order by <ordering col>) / 3.0)
from t;

If you do not care about the ordering but just the grouping into triads, you can use order by (select null).

And finally, SQL Server supports updatable CTEs and subqueries, so you can use this query in an update as well.

Upvotes: 1

Related Questions