CODEWITHSUNDEEP
sqlsqlite
Tuhin
Tuhin

Reputation: 157

How to RANK multiple columns of an entire table?

I have an SQLite table which is in the following format:

student physics chemistry maths history english

The student column has student name and the other columns have numeric values depicting score for the individual subjects. What I want to find out for each student is the rank of their subjects. For example, for the row,

Brian 78 62 100 40 50

I want to the output to be

Physics - 2
Chemistry - 3 
Maths - 1
History - 5
English - 4

which is the ranks of all the 5 subjects for each individual student. The output need not be in the format I showed. I just need an output that indicates the rank of each subject for each student. How can I achieve it in SQLite?

I found out RANK and ROW_NUMBER but didn't understand how to use them for multiple columns.

Upvotes: 3

Views: 201

Answers (1)

Wei Lin
Wei Lin

Reputation: 3811

My Logic

  • transposing columns into rows and get score column by union all
  • rank() over partition by student to get the ranks of all the 5 subjects for each individual student

Schema (SQLite v3.26)

CREATE TABLE Table1
    ("student" TEXT(5), "physics" INTEGER, "chemistry" INTEGER, "maths" INTEGER, "history" INTEGER, "english" INTEGER)
;

INSERT INTO Table1
    ("student", "physics", "chemistry", "maths", "history", "english")
VALUES
    ('Brian', 78, 62, 100, 40, 50),
    ('Henry', 55, 72, 85, 22, 50)
;

Query

with cte as (
  select student,'physics' as class,physics as score from Table1 union all
  select student,'chemistry' as class,chemistry as score  from Table1 union all
  select student,'maths' as class,maths as score  from Table1 union all
  select student,'history' as class,history as score  from Table1 union all
  select student,'english' as class,english as score  from Table1 
)
select  student,class,score,RANK() OVER (partition by student order by score desc) rnk
from cte;

| student | class     | score | rnk |
| ------- | --------- | ----- | --- |
| Brian   | maths     | 100   | 1   |
| Brian   | physics   | 78    | 2   |
| Brian   | chemistry | 62    | 3   |
| Brian   | english   | 50    | 4   |
| Brian   | history   | 40    | 5   |
| Henry   | maths     | 85    | 1   |
| Henry   | chemistry | 72    | 2   |
| Henry   | physics   | 55    | 3   |
| Henry   | english   | 50    | 4   |
| Henry   | history   | 22    | 5   |

View on DB Fiddle

Upvotes: 3

Related Questions