PostgreSQL CTE with ROW_NUMBER not returning expected sequential numbers after partitioning
I'm optimizing some code but Quick question that's been bugging me - I'm struggling with a Common Table Expression (CTE) in PostgreSQL 13 where I'm trying to assign a sequential row number to a result set partitioned by a specific column. The goal is to have each group of rows reset the numbering. However, when I run the following query, it seems that the `ROW_NUMBER()` is not behaving as I expected: ```sql WITH RankedEmployees AS ( SELECT id, name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY id) as rn FROM employees ) SELECT * FROM RankedEmployees; ``` In my `employees` table, I have several departments, and I want the `rn` column to start from 1 for each department. Instead, I'm getting results where the `rn` is jumping numbers between partitions, which is not what I expected. For example, if my `employees` table has the following data: | id | name | department | |----|--------|------------| | 1 | Alice | HR | | 2 | Bob | HR | | 3 | Charlie| Engineering | | 4 | David | Engineering | | 5 | Eve | HR | I was expecting `rn` to be: | id | name | department | rn | |----|--------|------------|----| | 1 | Alice | HR | 1 | | 2 | Bob | HR | 2 | | 5 | Eve | HR | 3 | | 3 | Charlie| Engineering | 1 | | 4 | David | Engineering | 2 | But instead, I'm getting incorrect row numbers, and I'm unsure why. I double-checked the partitioning and the ordering column. I've also tried removing the `ORDER BY` clause, thinking it might be causing the scenario, but that didn’t help. Any insights on why this might be happening or how to fix it would be really appreciated! What am I doing wrong? I'm using Sql stable in this project.