Скачать или смотреть Achieving Absolute Ranking in PostgreSQL Queries

Learn how to get absolute ranking in PostgreSQL with practical examples and clear explanations for better data analysis.
---
This video is based on the question https://stackoverflow.com/q/72650387/ asked by the user 'devcrazy' ( https://stackoverflow.com/u/11478858/ ) and on the answer https://stackoverflow.com/a/72650417/ provided by the user 'The Impaler' ( https://stackoverflow.com/u/6436191/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: PostgreSQL Absolute Ranking

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Achieving Absolute Ranking in PostgreSQL Queries

When working with databases, especially with systems like PostgreSQL, you might encounter a need to rank records based on certain criteria. In this post, we’ll dive into the problem of obtaining absolute rankings from a simple users table and how to structure your SQL queries to achieve the desired results.

Understanding the Problem

Let’s start by imagining a users table with the following structure:

IdNameRoomPoint1A1102B1203C2304D240In this example, we have users assigned to different rooms, along with their respective points. The objective is to retrieve not just the users in room 2 but also their rankings based on the points they have earned. However, we want to achieve an absolute ranking, meaning that the rank should reflect their position in the entire dataset, not just their room.

Initially, if you run the query:

[[See Video to Reveal this Text or Code Snippet]]

You’ll receive an output that looks like this:

IdNameRoomPointrnk3C23014D2402This shows relative rankings only among users in room 2, which is not what we want.

The Solution: Rank First, Filter Later

To correctly obtain absolute rankings, you need to calculate the rankings first for all users in the users table and then filter the results based on the room condition afterwards. Here’s how to structure your query:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Query

Subquery:

The inner SELECT statement computes the rank for all users. It utilizes the RANK() OVER(ORDER BY point) clause which ranks users based on their points in ascending order.

Outer Query:

The outer query filters the results to only include those who are in room 2.

Expected Results

When you execute the above query, you will receive:

IdNameRoomPointrnk3C23034D2404Now, the rankings reflect their position in the entire dataset, satisfying the requirement for absolute ranking.

Conclusion

In conclusion, obtaining an absolute ranking in PostgreSQL involves calculating ranks across the entire dataset before applying any filters. By employing a subquery strategy, you can achieve the expected results easily. This method is quite useful for data analysis where relative rankings alone are insufficient.

Now, as you implement this in your PostgreSQL queries, remember the phrase: Rank first, filter later. Happy querying!