CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Pரதீப்
Pரதீப்

Reputation: 93734

Generate Row Number for every 3 rows

I want generate number for every three rows 

CREATE TABLE #test(period INT)

INSERT INTO #test
VALUES      (602),(603),(604),(605),(606),(607),(608),(609)

I know we can generate sequence using row_number window function or while loop or cursor 

SELECT period,
       ( Row_number()OVER(ORDER BY period) - 1 ) / 3 + 1
FROM   #test

Result;

+--------+-----+
| period | seq |
+--------+-----+
|    602 |   1 |
|    603 |   1 |
|    604 |   1 |
|    605 |   2 |
|    606 |   2 |
|    607 |   2 |
|    608 |   3 |
|    609 |   3 |
+--------+-----+

Is there any other way to achieve this mathematically. There will not be any gaps between the periods

Upvotes: 2

Views: 9009

Answers (3)

Shaneis
Shaneis

Reputation: 1085

It is possible to achieve this with the NTILE() function but I don't think that it is more efficient than ROW_NUMBER(), mainly because this method has to get the total count to determine the amount of groups.

Create test environment:

/*  -- SQL 2016
DROP TABLE IF EXISTS #test;
GO
*/

IF OBJECT_ID('tempdb.dbo.#test') IS NOT NULL DROP TABLE #test

CREATE TABLE #test(period INT);
GO

INSERT INTO #test -- Make it bigger
VALUES      (602),(603),(604),(605),(606),(607),(608),(609);
GO 51

ROW_NUMBER Method:

SELECT /*ROW_NUM*/ period,
       ( Row_number()OVER(ORDER BY period) - 1 ) / 3 + 1
FROM   #test;
GO

ROW_NUMBER

IO and Time performances: Shortened for readability

(400 row(s) affected) Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0 '#test_00000000000E'. Scan count 1, logical reads 1, physical reads 0

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 85 ms.

NTILE Method

DECLARE @ntile_var int;

SELECT @ntile_var = COUNT(*) FROM #test;

SELECT /*NTILE*/period
    , NTILE(@ntile_var / 3) OVER (ORDER BY period)
FROM #test

IO and Time performances: Shortened for readability

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

Table '#test__00000000000E'. Scan count 1, logical reads 1, physical reads 0

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

(400 row(s) affected) Table 'Worktable'. Scan count 3, logical reads 811, physical reads 0 Table '#test___00000000000E'. Scan count 1, logical reads 1, physical reads 0

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 93

NTILE

Both of these give the same results:

Results_GroupsOf3

But there is a caveat! MSDN put it sufficiently as (emphasis added)

If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each.

So with the NTILE method, you could get a few groups of 4, so the rest of them can be 3.

Upvotes: 3

Peter B
Peter B

Reputation: 24222

A mathematical or arithmetic approach could be to use the period numbers themselves:

-- table init here
DECLARE @MIN_PERIOD INT = (SELECT MIN(period) FROM #test)

SELECT period,
       (period - @MIN_PERIOD) / 3 + 1 AS seq
FROM   #test

This works as long as "there will not be any gaps between the periods" remains true.

If you need a WHERE clause on the main query, also apply it to the SELECT MIN() query. Will work as long as the WHERE does not cause period gaps.

Upvotes: 6

M.Ali
M.Ali

Reputation: 69554

What about something like this... 

WITH X AS (
SELECT *
     ,ROW_NUMBER() OVER (ORDER BY [period] ASC) rn 
FROM #test
 )
 SELECT [period]
      ,ROW_NUMBER() OVER (PARTITION BY (X.rn % 3) ORDER BY rn ASC) rn 
FROM X
ORDER BY [period]

Upvotes: 2

Related Questions