t-SQL to find top X toppers in each subject from marks table

rank() function in SQL can be used to find such results in SQL!
Here is the simple example for FUN 🙂

use tempdb

create table studentMarks(studentName varchar(100), examSubject varchar(100), marks int)

insert studentMarks values ('Mike', 'English', 85)
insert studentMarks values ('Mike', 'History', 54)
insert studentMarks values ('Mike', 'Physics', 65)
insert studentMarks values ('Mike', 'Maths', 65)
insert studentMarks values ('Mike', 'Biology', 98)
insert studentMarks values ('Mike', 'Chemistry', 65)
insert studentMarks values ('Mike', 'Lab', 75)
insert studentMarks values ('Raj', 'English', 86)
insert studentMarks values ('Raj', 'History', 57)
insert studentMarks values ('Raj', 'Physics', 76)
insert studentMarks values ('Raj', 'Maths', 99)
insert studentMarks values ('Raj', 'Biology', 56)
insert studentMarks values ('Raj', 'Chemistry', 89)
insert studentMarks values ('Raj', 'Lab', 44)
insert studentMarks values ('Siri', 'English', 65)
insert studentMarks values ('Siri', 'History', 88)
insert studentMarks values ('Siri', 'Physics', 45)
insert studentMarks values ('Siri', 'Maths', 98)
insert studentMarks values ('Siri', 'Biology', 54)
insert studentMarks values ('Siri', 'Chemistry', 78)
insert studentMarks values ('Siri', 'Lab', 90)
insert studentMarks values ('Cortana', 'English', 92)
insert studentMarks values ('Cortana', 'History', 84)
insert studentMarks values ('Cortana', 'Physics', 76)
insert studentMarks values ('Cortana', 'Maths', 99)
insert studentMarks values ('Cortana', 'Biology', 65)
insert studentMarks values ('Cortana', 'Chemistry', 88)
insert studentMarks values ('Cortana', 'Lab', 100)
insert studentMarks values ('Vijred', 'English', 91)
insert studentMarks values ('Vijred', 'History', 57)
insert studentMarks values ('Vijred', 'Physics', 96)
insert studentMarks values ('Vijred', 'Maths', 98)
insert studentMarks values ('Vijred', 'Biology', 62)
insert studentMarks values ('Vijred', 'Chemistry', 71)
insert studentMarks values ('Vijred', 'Lab', 86)

-- SPOT the different between using ROW_NUMBER() and RANK() function
;WITH CTE AS 
(select studentName, examSubject, marks 
,ROW_NUMBER() OVER (PARTITION BY examSubject order by Marks desc) as myrank
from studentMarks)
select examSubject, studentName, marks from CTE where myrank <=2
order by examSubject, marks desc

;WITH CTE AS 
(select studentName, examSubject, marks 
,rank() OVER (PARTITION BY examSubject order by Marks desc) as myrank
from studentMarks)
select examSubject, studentName, marks from CTE where myrank <=2
order by examSubject, marks desc

drop table studentMarks

Reference/ New features in SQL 2012: https://vijredblog.wordpress.com/2014/02/18/tsql2012-less-frequently-used-features-revision-01/

Advertisements
This entry was posted in Script, SQL, SQL Query and tagged , , , . Bookmark the permalink.

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