Wednesday 21 November 2012

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>)

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...

ShareThis