Sample Header Ad - 728x90

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