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

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
needfor 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@officenet.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 |                                             |
------------------------+---------------------------------------------+