I've been trying to think of a use case for using ROW_NUMBER() with no ORDER
BY in the window clause.
Using the example table I always seem to be using, for those who missed it
in other threads.
create table employees ( id INT primary key, name varchar(30) not null, department varchar(30) not null, salary int not
null,check (salary >= 0)
);
insert into employees values(1,'Jeff','IT',10000);
insert into employees values(2,'Sam','IT',12000);
insert into employees values(3,'Richard','Manager',30000);
insert into employees values(4,'Ian','Manager',20000);
insert into employees values(5,'John','IT',60000);
insert into employees values(6,'Matthew','Director',60000);
david=# select *,row_number() over () from employees;id | name | department | salary | row_number
----+---------+------------+--------+------------ 1 | Jeff | IT | 10000 | 1 2 | Sam | IT
| 12000 | 2 4 | Ian | Manager | 20000 | 3 5 | John | IT | 60000 | 4 6
|Matthew | Director | 60000 | 5 3 | Richard | Manager | 30000 | 6
(6 rows)
row_number seems to assign the rows a number in order of how it reads them
from the heap. Just to confirm...
update employees set salary = salary where id = 3;
david=# select *,row_number() over () from employees;id | name | department | salary | row_number
----+---------+------------+--------+------------ 1 | Jeff | IT | 10000 | 1 2 | Sam | IT
| 12000 | 2 4 | Ian | Manager | 20000 | 3 5 | John | IT | 60000 | 4 6
|Matthew | Director | 60000 | 5 3 | Richard | Manager | 30000 | 6
(6 rows)
The spec says: "The ROW_NUMBER function computes the sequential row number,
starting with 1 (one) for the first row, of the row within its window
partition according to the window ordering of the window."
I'm just not sure if we should block this or not.
Does anyone see this as a feature?
Does anyone see this as a bug?
Any feedback is welcome
David.
