Marks and Ranks

    

Write an SQL query to rank the marks from the table. The marks should be ranked from highest to lowest and if the marks are same then they should be assigned the same rank. All the ranks of the marks should have consecutive integer values.

Input :-

Class table :-
ID is the Primary Key column for the Class table.

ID	Marks
1	98
2	90
3	94
4	89
5	94
6	89
7	83

Output :-

Marks	Rank
98	1
94	2
94	2
90	3
89	4
89	4
83	5

Query :-

select marks, 
dense_rank() over (order by marks desc) as 'Rank'
from Class;

Explanation : - 

This query is selecting the ‘marks’ column from a table called "Class" and applying the ‘dense_rank()’ function over the 'marks' column. The result of the function is aliased as ‘Rank’.

The ‘dense_rank()’ function is a window function that assigns a rank to each row based on the ordering specified in the ‘order by’ clause. In this case, the rows are ranked in descending order of the 'marks' column.

The ‘dense_rank()’ function differs from the traditional ‘rank()’ function in that it does not leave gaps in the ranking sequence when there are ties. For example, if two students have the same marks, they will be assigned the same rank, and the next student will be assigned the next consecutive rank.

So, the result of this query will be a list of marks from the "Class" table, along with the corresponding rank of each mark based on its value. The highest mark will have a rank of 1, the second-highest mark will have a rank of 2, and so on.