Sql Functions-RowNumber,Rank,Dense_Rank,NTile

Create table EMP(id int,name varchar(50),subject varchar(30),marks int)

——————————————————————————————–

INSERT INTO EMP VALUES(2,‘BUJJI’,‘MATHS’,49)
INSERT INTO EMP VALUES(2,‘BUJJI’,‘PHY’,43)
INSERT INTO EMP VALUES(2,‘BUJJI’,‘CHE’,46)
——————————————————————————————–
select * from EMP
–The result is

1 SHALINI MATHS 48
1 SHALINI PHY 50
1 SHALINI CHE 45
2 ANU MATHS 50
2 ANU PHY 49
2 ANU CHE 45
2 ARCHU MATHS 50
2 ARCHU PHY 45
2 ARCHU CHE 44
2 BUJJI MATHS 49
2 BUJJI PHY 43
2 BUJJI CHE 46

————————————————————————————————–
–Display the subject wise Toppers

select marks,name,SUBJECT,RANK from
(select RANK() OVER(PARTITION BY SUBJECT ORDER BY MARKS DESC) AS RANK , NAME,SUBJECT,MARKS FROMEMP )TEMP WHERE RANK=1
——————-
46 BUJJI CHE 1
50 ARCHU MATHS 1
50 ANU MATHS 1
50 SHALINI PHY 1
——————-
———————————————————————————————————-

–gIVING THE SUBJECT WISE RANKS

select marks,name,SUBJECT,RANK from (select
DENSE_RANK() OVER(PARTITION BY SUBJECT ORDER BY MARKS DESC) AS RANK , NAME,SUBJECT,MARKS FROM EMP )TEMP
——————————-

46 BUJJI CHE 1
45 SHALINI CHE 2
45 ANU CHE 2
44 ARCHU CHE 3
50 ARCHU MATHS 1
50 ANU MATHS 1
49 BUJJI MATHS 2
48 SHALINI MATHS 3
50 SHALINI PHY 1
49 ANU PHY 2
45 ARCHU PHY 3
43 BUJJI PHY 4
——————————–
————————————————————————————————————-
–Select Only one topper per subject, may be name can be priority when two have same marks
select marks,name,SUBJECT,RANK from (select
ROW_NUMBER() OVER(PARTITION BY SUBJECT ORDER BY MARKS DESC,NAME ASC)AS RANK , NAME,SUBJECT,MARKS FROM EMP )TEMP
WHERE RANK=1
————————-
46 BUJJI CHE 1
50 ANU MATHS 1
50 SHALINI PHY 1
————————–
————————————————————————————————————-
–DELETE DUPLICATE RECORDS IE..DELETE OTHER STUDENTS WHO ARE NOT TOPPERS

DELETE FROM EMP WHERE EXISTS( SELECT * FROM ( SELECT RANK()
OVER(PARTITION BY SUBJECT ORDER BY MARKS DESC) AS ROW,* from EMP )TEMP
WHERE ROW>1 AND EMP.ID=TEMP.ID)

Happy Reading


Advertisements