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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
