Row_number() in postgresql

Question

I have a query:

select .....
from....
order by ....

I would like to add row numbers to this query.

When I read about row_number() it says that the syntax is:

row_number() OVER(ORDER BY ...) 

meaning I have to do:

select row_number() OVER(ORDER BY ...) , .....
from....
order by ....

That means that I have to write twice the same ORDER BY statement.

Is there a way to just do something like:

select row_number() , .....
from....
order by ....

meaning that it will take whatever order that was given from the query and simply add numbers to it.

Answer 1

Postgres does allow the syntax:

select row_number() over (), . . .

However, that does not necessary return the row numbers in the ordering specified by the outer order by. I think Postgres calculates the row number before the order by.

You might be tempted to use:

select row_number() over (), . . .
from (select . . . 
      from . . .
      order by  . . .
     ) t;

And, this would seem to do what you want (one a single processor machine, for instance, it just did the right thing). However, this is not guaranteed to work. The ordering in a subquery does not apply to the outer query (although this might only be visible on multi-processor machines).

My advice? Simply repeat the order by twice (using either the expression or the alias). That is guaranteed to work. And, you might be surprised at the optimizer only needing to sort the data once.