How to get table records position in comparison to other records based on numeric field?

Hi,

Let's say I have 1000 registered users in database table and each of them has numeric ranking value.

How can I get the position of each user in comparison to other users ranking value?

If you're using SQL Server 2005, you can use the row_number() function. Are you?

Don

|||Hi,|||

Hi,

Yes, I am. Can you post some example code?

|||maybe you can post your table DDL, some sample data and the result that you want ?|||

Hi,

Solution found! Here's the code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>"

SelectCommand="SELECT [usernickname], ROW_NUMBER() OVER(ORDER BY userrankavg DESC) AS 'position' FROM [dbusers]"
</asp:SqlDataSource>

|||

Hi,

That doesn't actually solve my problem.

The code does display position for each record when all records are selected from table, but doesn't when WHERE attribute is specified.

How can I get position for a specific record that I specify with WHERE attribute?