Ranking Functions (Analytical Functions in Datawarehouse)

Here we will see how to find top two scores in each Region on the basis of their Marks using RANK Function

Select  Region ,Student, Sum_Marks Rank(Region,Sum_Marks)

From

(

Select  Region,Student,Sum(Marks)Sum_Marks

From Students

Group By Region,Student

Order By  Region,Sum(Marks) Desc

)

Where Rank(Region,Sum_Marks) < =2;

               

                                                                OR

SELECT * FROM

(

SELECT Region, Student,SUM(Marks) Sum_Marks,

RANK() OVER

(

PARTITION BY Region

ORDER BY Sum (Marks) DESC) Rank FROM Students

GROUP BY Region, Student

)

WHERE Rank <= 2;

Subject	Student	Sum_Marks
Sub_A	James	100
Sub_A	Ross	950
Sub_A	Verona	345
Sub_B	James	499
Sub_B	James	333
Sub_B	Daniel	399
Sub_B	Ross	553
Sub_C	James	200
Sub_C	Ross	100
Sub_C	Verona	120

 

If you don’t know about RANK function then, please check the below syntax

Multiple Column Rank Function

Rank (<Rank Expression>) Within Group

(Order By <Expression> <Asc|Desc> Nulls <First|Last>)

Or

Rank Analytic Function              

Rank() Over (<Query Partition Clause> <Order By Clause>)