Re: Windowing Function Patch Review -> ROW_NUMBER without ORDER BY

On Sunday 09 November 2008 22:35:01 David Rowley wrote:
> 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

I see this as a greate feature.
It will hopefully be possible to write:

SELECT *, max(row_number()) over() as total_rows from employees;

To get the maximum number of rows in a separate column. Very usefull when writing queries to retrieve "paged" results. Like "Give me the 20 top articles sorted on date and also the total number of articles" in *one* query, eliminating the need for a separate count(*) query.

There was some discussion regarding this here:
http://archives.postgresql.org/pgsql-hackers/2008-10/msg00729.php

--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / CEO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+