TSql Return column based on partition and rownumber

Question

I have a SQL server table where I'm attempting to get a computed column - MyPartition - indicating the number of the partition based on a variable @segment. For example, if @segment = 3 then the following output would be true.

RowID  | RowName    | MyPartition
------ | -----------| -------
1      | My Prod 1  | 1
2      | My Prod 2  | 1
3      | My Prod 3  | 1
4      | My Prod 4  | 2
5      | My Prod 5  | 2
6      | My Prod 6  | 2
7      | My Prod 7  | 3
8      | My Prod 8  | 3
9      | My Prod 9  | 3
10     | My Prod 10 | 4

What I have thus far is something like:

SELECT 
    RowId,
    RowName,
    ROW_NUMBER() OVER(PARTITION BY RowId ORDER BY RowId ASC) AS MyPartition
FROM MyTable
ORDER BY RowId

But as you can guess the partition just partitions on the rowid giving all values of mypartition = 1. I am unsure how to structure the partition by clause to achieve this.

Answer 1

How about

CEILING(ROW_NUMBER() OVER(ORDER BY RowId ASC) / 3.0)