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

>
>
> I see this as a greate feature.

I would treat ranking functions without explicit order by clause as a
feature rather than a bug. However, I believe, in most cases optimizer will
avoid additional sort when possible, thus an "order by" in a windowing
clause would not cause any performance degradation.

>
> It will hopefully be possible to write:
>
> SELECT *, max(row_number()) over() as total_rows from employees;

I believe this query does not make sense. At least, "row_number" without
"over" sounds odd.

To count all the rows (if you really want to) you might use "count(*) over
()".

>
> 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.

Sometimes it is better to perform several separate queries since optimizer
could use an index scan to get "20 top" and seq scan to get the "count(*)"

Regards,
Vladimir Sitnikov