1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

[SQL] Group by every N rows within a group

Discussão em 'Outras Linguagens' iniciado por Stack, Julho 21, 2021.

  1. Stack

    Stack Membro Participativo

    I have a requirement to group every N rows within each group of a SQL table, the best answer I've found is this; https://stackoverflow.com/a/66806186/10916933 but my data is also grouped by another identifier (Batch) and I would like to start a new GroupID every time the identifier changes. Batches are not of a consistent size.

    This is what I get currently (N = 2 for display purposes, it is actually 1000):

    Batch​
    Value​
    RowID​
    GroupID​
    A​
    10.2​
    1​
    1​
    A​
    6.7​
    2​
    1​
    A​
    7.6​
    3​
    2​
    B​
    7.0​
    4​
    2​
    B​
    10.2​
    5​
    3​
    C​
    9.6​
    6​
    3​
    C​
    8.2​
    7​
    4​
    C​
    1.1​
    8​
    4​
    C​
    0.3​
    9​
    5​
    C​
    9.0​
    10​
    5​
    C​
    10.2​
    11​
    6​

    This is what I want (i.e. when Batch B starts, it gives me a new GroupID even though there has only been one):

    Batch​
    Value​
    RowID​
    GroupID​
    A​
    10.2​
    1​
    1​
    A​
    6.7​
    2​
    1​
    A​
    7.6​
    3​
    2​
    B​
    7.0​
    4​
    3​
    B​
    10.2​
    5​
    3​
    C​
    9.6​
    6​
    4​
    C​
    8.2​
    7​
    4​
    C​
    1.1​
    8​
    5​
    C​
    0.3​
    9​
    5​
    C​
    9.0​
    10​
    6​
    C​
    10.2​
    11​
    6​

    Continue reading...

Compartilhe esta Página