Re: Windowing Function Patch Review -> ROW_NUMBER without ORDER BY - Mailing list pgsql-hackers

2008/11/10 David Rowley <dgrowley@gmail.com>:
> 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?

I don't see any reason to take it as a bug. It may be confusing some
people but it is consistent enough and not ambiguous. Many users
already know if they don't specify ORDER BY clause in a simple regular
query they wouldn't receive ordered rows so it will match their
senses.

Regards,

-- 
Hitoshi Harada