SQL Rank the columns based on values in each row

Question

I have a SQL table that looks similar to the sample below.

And for every row, I would want to add 4 columns that says the Rank of each subject (for a given row).

Is it possible to get this done in SQL? Kindly help. I use Vertica SQL.

Answer 1

One method is to unpivot the data and re-aggregate:

select name, 
       max(case when seqnum = 1 then subject end) as rank_1,
       max(case when seqnum = 2 then subject end) as rank_2,
       max(case when seqnum = 3 then subject end) as rank_3,
       max(case when seqnum = 4 then subject end) as rank_4
from (select s.*,
             row_number() over (partition by name order by score desc) as seqnum      
      from ((select name, 'English' as subject, english as score from t) union all
            (select name, 'Maths', maths from t) union all
            (select name, 'Science', science from t) union all
            (select name, 'Physics', Physics from t)
           ) s
     ) s
group by name;

Here is a db<>fiddle.

Answer 2

It's slightly more complicated; at least, it seems to me.

Breaking up into several sub-queries

WITH
-- your input - *please* paste it as text into your question next time - 
-- I had to re-type all of that instead of just reformatting it ...
-- and I use "nam" instead of "name", as it's a reserved word ...
indata(nam,english,science,physics,maths) AS (
            SELECT 'A',80,72,53,86
  UNION ALL SELECT 'B',60,65,75,52
  UNION ALL SELECT 'C',79,77,44,70
  UNION ALL SELECT 'D',69,69,55,65
)
,
-- add an in-line table with all subjects, to un-pivot with ...
subjects(subject) AS (
            SELECT 'english'
  UNION ALL SELECT 'science'
  UNION ALL SELECT 'physics'
  UNION ALL SELECT 'maths'
)
,
-- un-pivot, by CROSS JOINing and 
-- get a rank, based on combination of 
-- nam and the right mark based on subject ..
piv AS (
  SELECT
    *
  , ROW_NUMBER() OVER(
      PARTITION BY nam
      ORDER BY 
        CASE subject
          WHEN 'english' THEN english
          WHEN 'science' THEN science
          WHEN 'physics' THEN physics
          WHEN 'maths'   THEN maths
          ELSE 0
        END 
      DESC
    ) AS rnk
  FROM indata CROSS JOIN subjects
)
-- control query ....
-- SELECT * FROM piv ORDER BY nam,rnk;
-- out  nam | english | science | physics | maths | subject | rnk 
-- out -----+---------+---------+---------+-------+---------+-----
-- out  A   |      80 |      72 |      53 |    86 | maths   |   1
-- out  A   |      80 |      72 |      53 |    86 | english |   2
-- out  A   |      80 |      72 |      53 |    86 | science |   3
-- out  A   |      80 |      72 |      53 |    86 | physics |   4
-- out  B   |      60 |      65 |      75 |    52 | physics |   1
-- out  B   |      60 |      65 |      75 |    52 | science |   2
-- out  B   |      60 |      65 |      75 |    52 | english |   3
-- out  B   |      60 |      65 |      75 |    52 | maths   |   4
-- out  C   |      79 |      77 |      44 |    70 | english |   1
-- out  C   |      79 |      77 |      44 |    70 | science |   2
-- out  C   |      79 |      77 |      44 |    70 | maths   |   3
-- out  C   |      79 |      77 |      44 |    70 | physics |   4
-- out  D   |      69 |      69 |      55 |    65 | english |   1
-- out  D   |      69 |      69 |      55 |    65 | science |   2
-- out  D   |      69 |      69 |      55 |    65 | maths   |   3
-- out  D   |      69 |      69 |      55 |    65 | physics |   4

-- finally, re-pivot, by GROUP-ing by the original columns of "indata"
-- and applying a MAX(CASE ...) to the columns I want to add.
SELECT
  nam
, english
, science
, physics
, maths
, MAX(CASE rnk WHEN 1 THEN subject END) AS rank_1
, MAX(CASE rnk WHEN 2 THEN subject END) AS rank_2
, MAX(CASE rnk WHEN 3 THEN subject END) AS rank_3
, MAX(CASE rnk WHEN 4 THEN subject END) AS rank_4
FROM piv
GROUP BY
  nam
, english
, science
, physics
, maths
ORDER BY nam;
-- out  nam | english | science | physics | maths | rank_1  | rank_2  | rank_3  | rank_4  
-- out -----+---------+---------+---------+-------+---------+---------+---------+---------
-- out  A   |      80 |      72 |      53 |    86 | maths   | english | science | physics
-- out  B   |      60 |      65 |      75 |    52 | physics | science | english | maths
-- out  C   |      79 |      77 |      44 |    70 | english | science | maths   | physics
-- out  D   |      69 |      69 |      55 |    65 | english | science | maths   | physics