RANK Function

Today(9-Oct-13) One of my friend asked me how to query to find out rank  among student.

 

Let us assume there is two tables. 

 

 

Table: Student                                             Table: Marks

 

Column:                                                          Column:

 

Stu_ID  --> PK                                               Stu_ID ---> FK



Stu_Name                                                       Marks

 

 

Need to  Select Stu_ID, Stu_Name, Marks and their Rank.

 

T-SQL Script:

 

T-SQL provides RANK() Function. 

 

SELECT

   M.Stu_ID

  ,S.Stu_Name

  ,M.Marks

  ,RANK() OVER

  ( ORDER BY M.Marks DESC) AS RANK

  FROM Marks As M

  INNER JOIN Student As S

  ON M.Stu_ID = S.[Stu-ID]

  WHERE M.Stu_ID = S.[Stu-ID]

  ORDER BY M.Stu_ID

 

Output: