CODEWITHSUNDEEP
sqlpostgresql
devcrazy
devcrazy

Reputation: 503

PostgreSQL Absolute Ranking

The users table is like this:

Id Name Room Point
1 A 1 10
2 B 1 20
3 C 2 30
4 D 2 40

I want to get ranking with some conditions. The query is SELECT *, RANK() OVER (ORDER BY users.point ASC) rnk FROM users WHERE users.room = 2
Then the ranking column(rnk) is not a absolute ranking. The query result is

Id Name Room Point rnk
3 C 2 30 1
4 D 2 40 2

But I want absolute ranking, and the expected result is

Id Name Room Point rnk
3 C 2 30 3
4 D 2 40 4

Upvotes: 0

Views: 149

Answers (1)

The Impaler
The Impaler

Reputation: 48865

Rank first, filter later. For example:

select *
from (
  select *, rank() over(order by point) as rnk
  from users
) x
where room = 2

Upvotes: 1

Related Questions