Ranking Functions (SQL Partitions)

http://www.besttechtools.com/articles/article/sql-rank-functions

-- Test table
declare @ExamResults table(
        studentName varchar(50),
        subjectName varchar(20),
        marks int)

insert into @ExamResults values('Adam','Maths',70)
insert into @ExamResults values ('Adam','Science',80)
insert into @ExamResults values ('Adam','Social',60)

insert into @ExamResults values('Rak','Maths',60)
insert into @ExamResults values ('Rak','Science',50)
insert into @ExamResults values ('Rak','Social',70)

insert into @ExamResults values('Sam','Maths',90)
insert into @ExamResults values ('Sam','Science',90)
insert into @ExamResults values ('Sam','Social',80)

select studentName, subjectName, marks
from @ExamResults

-- Ranking Functions
select studentName, subjectName, marks,

-- Row "qualification" within a subset of record having its same studentName value, order by marks.
-- Rows with the same marks value have the same rank; in this case the next rank will "jump" a position
RANK() over(partition by studentName order by marks desc) [rank],

-- The same as RANK(), but in this case if rows with the same marks value are present there's no "jump"
DENSE_RANK() over(partition by studentName order by marks desc) denseRank,

-- Row group number over a specified number of groups. Useful to split the rows into set of groups
NTILE(2) over(partition by studentName order by marks desc) ntile2,
NTILE(3) over(partition by studentName order by marks desc) ntile3,

-- Row number. If rows with the same marks value are present they will have different row numbers
ROW_NUMBER() over(partition by studentName order by marks desc) rowNumber
from @ExamResults

-- Example: list of students with just the subjects in wich the have the highest marks

-- 1) Solution without using Ranking Functions
select E1.studentName, E2.subjectName, E1.marks
from (
        select studentName, MAX(marks) marks
        from @ExamResults
        group by studentName
) E1
inner join @ExamResults E2 on E2.studentName = E1.studentName and E2.marks = E1.marks
order by studentName

-- 2) Solution using Ranking Functions
select E.studentName, E.subjectName, E.marks
from (
        select studentName, subjectName, marks,
        RANK() over (partition by studentName order by marks desc) [rank]
        from @ExamResults
) E
where E.[rank] = 1

And here is the result:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s