Return row position - Postgres

Question

I return a table with position:

select * 
from (
    select *, row_number() over() as position 
    from organization
) result 
where data1 = 'Hello';

Gives back this, which is correct:

data1 | Hello
data2 | Joe
position | 5

But when I do:

select position 
from (
   select *, row_number() over() as position 
   from organization
) result 
where data1 = 'Hello';

It returns:

position | 25

What am missing here? How can I modify this query to return 5?

Answer 1

A table in RDBMS is an unordered set of rows. Without an order by clause in the row_number, it will assign row numbers arbitrarily.

Use proper order by clause to get consistent results:

select position 
from (
   select *,
        row_number() over(
           order by ??   -- add column(s) here
        ) as position 
   from organization
) result 
where data1 = 'Hello';