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?

You should include sorting within the window function with an ORDER BY clause. Without it the result is arbitrary. — Kamil Gosciminski
– Kamil Gosciminski, Commented Mar 27, 2017 at 17:54

Gurwinder Singh · Accepted Answer · 2017-03-27 17:59:10Z

11

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';

edited Mar 27, 2017 at 17:59

answered Mar 27, 2017 at 17:54

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Anton Kim Over a year ago

Thanks, I want the ordering to be the same as in the table "organization" right now, the reason is that other application lists records one by one from top to bottom as it is now. Which "order by" can I use in this case?

user330315 Over a year ago

@AntonKim: there is no such thing as "the order in the table".

Gurwinder Singh Over a year ago

@AntonKim - A table in RDBMS is an unordered set of rows. There is no such thing as table order. You need to define which column you want to order by.

Anton Kim Over a year ago

@GurV ok thanks, is there any other option since the app just lists records from select * from..... without order by to mimic that?

user330315 Over a year ago

@AntonKim: the only way (really: the only) to get a defined sort order is to use order by. There is no alternative.

|

Collectives™ on Stack Overflow

Return row position - Postgres

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related