CODEWITHSUNDEEP
sql
Cyan
Cyan

Reputation: 71

SQL, how to order by score and get position of ID in ordered list

This is what I have:

SELECT * FROM scores WHERE userId ="${message.author.id}"

This will get the row in the table scores that has the userId of ${message.author.id}.

I want to use this:

SELECT * FROM scores ORDER BY points

Now I don't know what to do from here. I would like to take this ordered list and get the position of the row with the userId of ${message.author.id}.

So I basically want to order the table by the amount of points each user has, then I want to use a userId to see what position that user is in.

EXAMPLE:

userId   points
1         20
2         30
3         10
4         25

when I use this: 

SELECT * FROM scores ORDER BY points DESC

it should now look like:

userId   points
2         30
4         25
1         20
3         10

Then I want to get the position of the row a specific userId is in. So If I get the row of userId = 1 I want to get out: 3.

How would I do this? Thanks.

Upvotes: 3

Views: 2315

Answers (3)

JuanR
JuanR

Reputation: 7783

You can use the following for SQL Server:

SELECT 
    ROW_NUMBER() OVER(ORDER BY points) AS RowId, * 
FROM 
    scores 
ORDER BY 
    points

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use ANSI standard window functions (row_number() or rank()) for the ranking for everyone:

select s.*, rank() over (order by points desc) as ranking
from scores s;

(rank() allows ties; row_number() gives tied scores different but adjacent rankings.)

For a specific user:

select count(*) + 1
from scores s
where s.score > (select s2.score from scores s where s2.userId = @userId);

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You need a ranking function, which can be done with a correlated subquery.

select userid,points,
(select count(distinct points) from scores where points >= s.points) as rnk
from scores s

Then query for required userId's.

select rnk 
from (select userid,points,
      (select count(distinct points) from scores where points >= s.points) as rnk
      from scores s 
     ) t
where userid=1 --change this as required

If your dbms supports window functions, use dense_rank.

select rnk
from (select userid,points,dense_rank() over(order by points desc) as rnk
      from scores s
     ) t
where userid=1

Upvotes: 1

Related Questions