SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Class(Class INT, Roll INT, Subject VARCHAR(1), Marks INT);
INSERT INTO Class(Class, Roll, Subject, Marks) 
VALUES
(1,1,'M',45),
(1,1,'H',45),
(1,1,'G',76),
(1,1,'P',80),

(1,2,'M',65),
(1,2,'H',25),
(1,2,'G',56),
(1,2,'P',70),

(2,1,'M',35),
(2,1,'H',75),
(2,1,'G',77),
(2,1,'P',100),

(2,3,'M',15),
(2,3,'H',37),
(2,3,'G',79),
(2,3,'P',85)
;
16 rows affected

select * from Class


Class
Roll
Subject
Marks


1
1
M
45

1
1
H
45

1
1
G
76

1
1
P
80

1
2
M
65

1
2
H
25

1
2
G
56

1
2
P
70

2
1
M
35

2
1
H
75

2
1
G
77

2
1
P
100

2
3
M
15

2
3
H
37

2
3
G
79

2
3
P
85


select * , 
Row_number() Over(Partition by Class, Roll order by Class, Roll) as RowNum,
RANK() Over(Partition by Class, Roll order by Class, Roll, Marks) as RankNum,
DENSE_RANK() Over(Partition by Class, Roll order by Class, Roll, Marks) as DenseRankNum
from Class


Class
Roll
Subject
Marks
RowNum
RankNum
DenseRankNum


1
1
M
45
1
1
1

1
1
H
45
2
1
1

1
1
G
76
3
3
2

1
1
P
80
4
4
3

1
2
H
25
1
1
1

1
2
G
56
2
2
2

1
2
M
65
3
3
3

1
2
P
70
4
4
4

2
1
M
35
1
1
1

2
1
H
75
2
2
2

2
1
G
77
3
3
3

2
1
P
100
4
4
4

2
3
M
15
1
1
1

2
3
H
37
2
2
2

2
3
G
79
3
3
3

2
3
P
85
4
4
4

Class	Roll	Subject	Marks
1	1	M	45
1	1	H	45
1	1	G	76
1	1	P	80
1	2	M	65
1	2	H	25
1	2	G	56
1	2	P	70
2	1	M	35
2	1	H	75
2	1	G	77
2	1	P	100
2	3	M	15
2	3	H	37
2	3	G	79
2	3	P	85

Class	Roll	Subject	Marks	RowNum	RankNum	DenseRankNum
1	1	M	45	1	1	1
1	1	H	45	2	1	1
1	1	G	76	3	3	2
1	1	P	80	4	4	3
1	2	H	25	1	1	1
1	2	G	56	2	2	2
1	2	M	65	3	3	3
1	2	P	70	4	4	4
2	1	M	35	1	1	1
2	1	H	75	2	2	2
2	1	G	77	3	3	3
2	1	P	100	4	4	4
2	3	M	15	1	1	1
2	3	H	37	2	2	2
2	3	G	79	3	3	3
2	3	P	85	4	4	4

Class	Roll	Subject	Marks
1	1	M	45
1	1	H	45
1	1	G	76
1	1	P	80
1	2	M	65
1	2	H	25
1	2	G	56
1	2	P	70
2	1	M	35
2	1	H	75
2	1	G	77
2	1	P	100
2	3	M	15
2	3	H	37
2	3	G	79
2	3	P	85

Class	Roll	Subject	Marks	RowNum	RankNum	DenseRankNum
1	1	M	45	1	1	1
1	1	H	45	2	1	1
1	1	G	76	3	3	2
1	1	P	80	4	4	3
1	2	H	25	1	1	1
1	2	G	56	2	2	2
1	2	M	65	3	3	3
1	2	P	70	4	4	4
2	1	M	35	1	1	1
2	1	H	75	2	2	2
2	1	G	77	3	3	3
2	1	P	100	4	4	4
2	3	M	15	1	1	1
2	3	H	37	2	2	2
2	3	G	79	3	3	3
2	3	P	85	4	4	4

Class	Roll	Subject	Marks
1	1	M	45
1	1	H	45
1	1	G	76
1	1	P	80
1	2	M	65
1	2	H	25
1	2	G	56
1	2	P	70
2	1	M	35
2	1	H	75
2	1	G	77
2	1	P	100
2	3	M	15
2	3	H	37
2	3	G	79
2	3	P	85

Class	Roll	Subject	Marks	RowNum	RankNum	DenseRankNum
1	1	M	45	1	1	1
1	1	H	45	2	1	1
1	1	G	76	3	3	2
1	1	P	80	4	4	3
1	2	H	25	1	1	1
1	2	G	56	2	2	2
1	2	M	65	3	3	3
1	2	P	70	4	4	4
2	1	M	35	1	1	1
2	1	H	75	2	2	2
2	1	G	77	3	3	3
2	1	P	100	4	4	4
2	3	M	15	1	1	1
2	3	H	37	2	2	2
2	3	G	79	3	3	3
2	3	P	85	4	4	4