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 |