Is there a better SQL query to find ranking of a single row in database?
2
votes
1
answer
911
views
I would like to obtain the ranking of a user's points in their country. For example, 23rd highest points in the US.
After some Googling, I found that the
RANK
function would be the best solution. Here is my SQL query:
SELECT points_ranking FROM
(
SELECT
user_country,
user_id,
user_points,
RANK () OVER (
ORDER BY user_points DESC
) points_ranking
FROM data.user
WHERE
user_country iLIKE 'usa'
) as subquery
WHERE
user_id = 3
However, I'm unsure if this is highly inefficient or if there's a better way. I ask this because I'm concerned the inner query would be ranking every single row in the table, just to select one.
Asked by nreh
(123 rep)
Nov 29, 2022, 02:10 AM
Last activity: Nov 29, 2022, 05:14 AM
Last activity: Nov 29, 2022, 05:14 AM