partition by - How to group and number SQL results? - Stack Overflow

admin管理员组

文章数量:1310178

I thought that PARTITION BY is supposed to group(partition) the results by a 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

I thought that PARTITION BY is supposed to group(partition) the results by a 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

• sql
• partition-by

Share Improve this question Follow edited Feb 4 at 6:15 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Feb 3 at 17:56 ojekojek 10.1k2222 gold badges7474 silver badges115115 bronze badges 2

• 3 Partitioning and ordering by the same column makes no sense. – Charlieface Commented Feb 3 at 18:09
• 1 Please tag your rdbms – Dale K Commented Feb 4 at 6:02

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 5

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

本文标签： partition byHow to group and number SQL resultsStack Overflow