How to write a SQL query to find top n values in each row of a table?

Problem Description:

Let consider the following table that represents the grade of students for different courses:

The question is how the grades can be sorted in the descending order for each student. The desired output should be as follows:

Solution:

To sort records based on only one course, let say Physics, the following SQL query can be used in Postgres:

SELECT *
FROM
  grades
ORDER BY Physics  DESC

As a result, top grades for Physics course are sorted in the descending order.

To obtain the desired solution, first a temporary table should be created and then the grades will be sorted in the descending order. Since “for” loop cannot be used in SQL queries to find each of the grades and store them in different columns, similar queries with different offsets are used. Hence, the following query can be used:

SELECT *,

(SELECT Max(x)
        FROM (VALUES (Biology),(Physics),(Calculus)) AS Y(x) ) AS highest_grade
,
(SELECT x
        FROM (VALUES (Biology),(Physics),(Calculus)) AS Y(x) ORDER BY x DESC OFFSET 1 ROWS FETCH next 1 ROWS ONLY ) AS second_highest_grade,
(SELECT x
        FROM (VALUES (Biology),(Physics),(Calculus)) AS Y(x) ORDER BY x DESC OFFSET 2 ROWS FETCH next 1 ROWS ONLY ) AS third_highest_grade

FROM
(SELECT *
FROM grades) AS T

The source code of this project is available in my Github page.