SQL Server Help Required

I have a record of 10 students.

id  markss
1   50
2   60
3   40
4   78
5   45
6   55
7   35
8   86
9   开发者_StackOverflow中文版56
10  83

Now I want to show the Positions i.e First,2nd and 3rd position of students. Can anyone help me to find out this in SQL Server.

one way, use DENSE_RANK() if you have people with the same score, or plain RANK() if you want gaps...

SELECT id, markss, DENSE_RANK() OVER(ORDER BY markss DESC) AS position
FROM myTable
ORDER BY markss DESC

example

CREATE TABLE #Mytable (id int,markss INT)
INSERT #Mytable VALUES(1,  50)
INSERT #Mytable VALUES(2, 60)
INSERT #Mytable VALUES(3, 40)
INSERT #Mytable VALUES(4,  78)
INSERT #Mytable VALUES(5,  45)
INSERT #Mytable VALUES(6,  55)
INSERT #Mytable VALUES(7,  35)
INSERT #Mytable VALUES(8,  86)
INSERT #Mytable VALUES(9,  56)
INSERT #Mytable VALUES(10, 83)

now add a duplicate score

INSERT #Mytable VALUES(11,  86)

query using 3 ranking functions

SELECT id, markss, 
DENSE_RANK() OVER(ORDER BY markss DESC) AS DensePosition,
RANK() OVER(ORDER BY markss DESC) AS RankPOSITION,
ROW_NUMBER() OVER(ORDER BY markss DESC) AS RowPosition
FROM #Mytable
ORDER BY markss DESC

Output

8   86  1   1   1
11  86  1   1   2
10  83  2   3   3
4   78  3   4   4
2   60  4   5   5
9   56  5   6   6
6   55  6   7   7
1   50  7   8   8
5   45  8   9   9
3   40  9   10  10
7   35  10  11  11

For SQL Server 2005+:

select id, markss, row_number() over (order by markss desc) as Position
    from YourTable
    order by Position