Subject Re: Odp: [firebird-support] Re: FB30 question about row_number
Author philippe makowski
liviuslivius@... [2013-08-08 22:24] :
> Hi,
> I know how to do select with this, but i ask how should row_number work without specify order by in over clause. It numerate records in some internal order or it should numerate it in retrival order.
> You do select an without order you should have always sequence 1 2 3 4? And only when you specify order in over clause you can get it as e.g. 2 4 1 3?

ROW_NUMBER: Assigns a sequential number to each row in the result set,
or said another way number of the current row within its partition,
counting from 1

so a sequential number for the set (partition) involved.
Ordering is used for order-relative functions such as row_number.

like for "Select First ..." or "Select .. rows", an Order BY is required
if you want something predictible

I guess that what it is said for MsSQL is also true for Firebird :

There is no guarantee that the rows returned by a query using
ROW_NUMBER() will be ordered exactly the same with each execution unless
the following conditions are true.

Values of the partitioned column are unique.

Values of the ORDER BY columns are unique.

Combinations of values of the partition column and ORDER BY columns
are unique.