I apparently don't understand why PARTITION BY exists. I thought that it's supposed to group(partition) the results by specific column, so that if row_number() is applied over that, it will number the groups(partitions) of items. This obviously is not the case. Can someone please explain to me, what is partition by supposed to be doing?
How can I achieve my expected result, where X column is numbered/counted?
WITH cte(X,Y) AS
(
SELECT 10 AS X, 1 AS Y UNION ALL
SELECT 10 AS X, 2 AS Y UNION ALL
SELECT 10 AS X, 3 AS Y UNION ALL
SELECT 10 AS X, 4 AS Y UNION ALL
SELECT 10 AS X, 5 AS Y UNION ALL
SELECT 20 AS X, 1 AS Y UNION ALL
SELECT 20 AS X, 2 AS Y UNION ALL
SELECT 20 AS X, 3 AS Y UNION ALL
SELECT 20 AS X, 4 AS Y UNION ALL
SELECT 20 AS X, 5 AS Y
)
SELECT cte.*,
ROW_NUMBER() OVER (PARTITION BY cte.X ORDER BY cte.X) AS [GROUP_NUMBER]
FROM cte
Actual result:
X Y GROUP_NUMBER
10 2 1
10 3 2
10 4 3
10 5 4
10 1 5
20 1 1
20 2 2
20 3 3
20 4 4
20 5 5
Expected result:
X Y GROUP_NUMBER
10 2 1
10 3 1
10 4 1
10 5 1
10 1 1
20 1 2
20 2 2
20 3 2
20 4 2
20 5 2
Since ROW_NUMBER() always increments per each groupings, you actually seem to use DENSE_RANK() function for the desired result. You also need to order by X not partition by it.
SELECT cte.*, DENSE_RANK() OVER (ORDER BY X) AS group_number
FROM cte