CODEWITHSUNDEEP
sqlsql-server
Bob
Bob

Reputation: 63

How to group numbered rows into groups

In Sql Server I need to take repeating sets of row numbers and group those into segments or sub groups. I'm trying to achieve column B using Sql. I've achieved column a using the row_number() function but I'm not sure how to get to Column B.

Here is the logic for row_number()

1 + ((row_number() over (order by TimeStamp, FileName, OrderID) - 1) % 5) AS [Row_Number]

enter image description here

Upvotes: 0

Views: 160

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Your row_number() is of the form:

row_number() over (partition by colA order by colB)

What you seem to want is:

dense_rank() over (order by colA)

That is, the partition key(s) used for the row_number() should be the order by keys for the dense_rank().

EDIT:

Your code is:

1 + ((row_number() over (order by TimeStamp, FileName, OrderID) - 1) % 5) AS [Row_Number]

In this case, there is no partition by. What you really want simply integer division. This is easy:

1 + ((row_number() over (order by TimeStamp, FileName, OrderID) - 1) / 5) AS [Row_Number]

Upvotes: 2

Jaaz Cole
Jaaz Cole

Reputation: 3180

I would go with a simple solution:

SELECT [Row_Number], GroupNumber
FROM (
    SELECT [Row_Number]
        , row_number()over(partition by [Row_Number] order by [Row_Number]) as GroupNumber
        --Note: The order by clause above should be replaced with however you are ordering the groups of row numbers)
    FROM YourTableOrInlineView
    ) z
ORDER BY GroupNumber, [Row_Number]

Upvotes: 0

Related Questions