How can I get row's index from a table in SQL Server?

Question

I have a Toplist table and I want to get a user's rank. How can I get the row's index?

Unfortunately, I am getting all rows and checking in a for loop the user's ID, which has a significant impact on the performance of my application.

How could this performance impact be avoided?

A rank is not the same as a row-number. http://technet.microsoft.com/en-us/library/ms189798.aspx — Tim Schmelter, Nov 06 '13 at 08:32
As Tim is 100% correct, i think in a "toplist" the row number becomes the rank? — Mad Dog Tannen, Nov 06 '13 at 08:43

score 27 · Accepted Answer · answered Nov 06 '13 at 08:24

You can use ROW.NUMBER

This is a example syntax for MySQL

SELECT  t1.toplistId, 
        @RankRow := @RankRow+ 1 AS Rank
FROM    toplist t1
JOIN    (SELECT @RankRow := 0) r;

This is a example syntax for MsSQL

SELECT ROW_NUMBER() OVER(ORDER BY YourColumn) AS Rank,TopListId
FROM TopList

score 9 · Answer 2 · answered Mar 11 '20 at 23:30

9

You may also do something like this:

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS MyIndex
FROM TopList

answered Mar 11 '20 at 23:30

BICube

score 5 · Answer 3 · answered Nov 06 '13 at 08:25

5

Use in your code analytic functions

answered Nov 06 '13 at 08:25

realnumber3012

3 Answers3