SQL Server: position based on marks

Question

I am using SQL Server 2008. I have a Student table in which there are following fields: 1. StudentId, 2. StudentName, 3. Marks . I want to get a resultset in which there should be a column named “Position”. Something like “Select StudentId,StudentName,Marks, as Position from Student...” so that, depending on the marks a student scored, i can evaluate them as the 1st, 2nd or 20th position. If students have the same marks, then they have the same position. Thanks. Rhys

Answer 1

Use RANK:

If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

The query could look like this:

SELECT
    StudentId,
    StudentName,
    Marks,
    RANK() OVER (ORDER BY Marks DESC) AS Position
FROM Student

Answer 2

You can use a subquery to calculate the position. The position is simply one more than the number of students with a higher mark:

select
  s.StudentId, s.StudentName, s.Marks,
  Position = 1 + (select count(*) from Students m where m.Marks > s.Marks)
from
  Students s