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

sql sql-server row rank

Tugrul Emre Atalay · Nov 6, 2013 · Viewed 46.5k times · Source

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?

Answer

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