CODEWITHSUNDEEP
sqlsql-serverselect
user7432713
user7432713

Reputation: 226

SQL Server : create group of N rows each and give group number for each group

I want to create a SQL query that SELECT a ID column and adds an extra column to the query which is a group number as shown in the output below.

Each group consists of 3 rows and should have the MIN(ID) as a GroupID for each group. The order by should be ASC on the ID column.

ID   GroupNr
------------
100   100
101   100
102   100
103   103
104   103
105   103
106   106
107   106
108   106

I've tried solutions with ROW_NUMBER() and DENSE_RANK(). And also this query:

SELECT 
    *, MIN(ID) OVER (ORDER BY ID ASC ROWS 2 PRECEDING) AS Groupnr
FROM   
    Table
ORDER BY 
    ID ASC

Upvotes: 0

Views: 2382

Answers (3)

Umair Zafar
Umair Zafar

Reputation: 90

SELECT  T2.ID, T1.ID
FROM    (
            SELECT  MIN(ID) AS ID, GroupNr
            FROM
                    (
                        SELECT ID, ( Row_number()OVER(ORDER BY ID) - 1 ) / 3 + 1 AS GroupNr
                        FROM   Table
                    ) AS T1
            GROUP BY GroupNr
        ) AS T1
        INNER JOIN (
            SELECT ID, ( Row_number()OVER(ORDER BY ID) - 1 ) / 3 + 1 AS GroupNr
            FROM   Table
        ) T2 ON T1.GroupNr = T2.GroupNr

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Use row_number() to enumerate the rows, arithmetic to assign the group and then take the minimum of the id:

SELECT t.*, MIN(ID) OVER (PARTITION BY grp) as groupnumber       
FROM (SELECT t.*,
            ( (ROW_NUMBER() OVER (ORDER BY ID) - 1) / 3) as grp
      FROM Table
     ) t
ORDER BY ID ASC;

It is possible to do this without a subquery, but the logic is rather messy:

select t.*,
       (case when row_number() over (order by id) % 3 = 0
             then lag(id, 2) over (order by id)
             when row_number() over (order by id) % 3 = 2
             then lag(id, 1) over (order by id)
             else id
        end) as groupnumber
from table t
order by id;

Upvotes: 3

Thom A
Thom A

Reputation: 95588

Assuming you want the lowest value in the group, and they are always groups of 3, rather than the NTILE (as Saravantn suggests, which splits the data into that many even(ish) groups), you could use a couple of window functions:

WITH Grps AS(
    SELECT V.ID,
           (ROW_NUMBER() OVER (ORDER BY V.ID) -1) / 3 AS Grp
    FROM (VALUES(100),
                (101),
                (102),
                (103),
                (104),
                (105),
                (106),
                (107),
                (108))V(ID))
SELECT G.ID,
       MIN(G.ID) OVER (PARTITION BY G.Grp) AS GroupNr
FROM Grps G;

Upvotes: 1

Related Questions