How to Retrieve Rank Based on Total Mark in SQLite Table

Question

say I have a table like this:

 S_id  |ca1 |ca2 |exam
  1    | 08 | 12 | 35
  1    | 02 | 14 | 32
  1    | 08 | 12 | 20
  2    | 03 | 11 | 55
  2    | 09 | 18 | 45
  2    | 10 | 12 | 35
  3    | 07 | 12 | 35
  3    | 04 | 14 | 37
  3    | 09 | 15 | 32
  4    | 03 | 11 | 55
  4    | 09 | 18 | 45
  4    | 10 | 12 | 35
  5    | 10 | 12 | 35
  5    | 07 | 12 | 35
  5    | 09 | 18 | 45

I want to select S_id, total and assign a rank on each student based on sum(ca1+ca2+exam) like the following:

 S_id |total|rank
   1  | 158 | 5
   2  | 198 | 1
   3  | 165 | 4
   4  | 198 | 1
   5  | 183 | 3

If there are the same total, like S_id 2 and S_id 4 with rank 1, I want the rank to be jumped to 3.

Thanks for helping.

Answer 1

Make the table:

sqlite> create table t (S_id, ca1, ca2, exam);
sqlite> insert into t values 
   ...> ( 1 , 08 , 12 , 35 ),
   ...> ( 1 , 02 , 14 , 32 ),
   ...> ( 1 , 08 , 12 , 20 ),
   ...> ( 2 , 03 , 11 , 55 ),
   ...> ( 2 , 09 , 18 , 45 ),
   ...> ( 2 , 10 , 12 , 35 ),
   ...> ( 3 , 07 , 12 , 35 ),
   ...> ( 3 , 04 , 14 , 37 ),
   ...> ( 3 , 09 , 15 , 32 ),
   ...> ( 4 , 03 , 11 , 55 ),
   ...> ( 4 , 09 , 18 , 45 ),
   ...> ( 4 , 10 , 12 , 35 ),
   ...> ( 5 , 10 , 12 , 35 ),
   ...> ( 5 , 07 , 12 , 35 ),
   ...> ( 5 , 09 , 18 , 45 );

Make a temporary table with the total scores:

sqlite> create temp table tt
        as select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
        from t group by S_id;

Use the temporary table to compute the ranks:

sqlite> select s.S_id, s.total,
          (select count(*)+1 from tt as r where r.total > s.total) as rank
          from tt as s;
1|143|5
2|198|1
3|165|4
4|198|1
5|183|3

Drop the temporary table:

sqlite> drop table tt;

ADDENDUM

With a recent change (2015-02-09) to SQLite, this formulation now works:

with tt (S_id, total) as 
   (select S_id, sum(ca1 + ca2 + exam) as total from t group by S_id)
select s.S_id, s.total,
       (select count(*)+1 from tt as r where r.total > s.total) as rank
   from tt as s;

Answer 2

Per my standard Rank Rows answer, use a self join:

with tt (S_id, total) as
   (select S_id, sum(ca1 + ca2 + exam) as total
       from t group by S_id)
select S.S_id, S.total, 1+count(lesser.total) as RANK
        from tt as S
        left join tt as lesser
        on   S.total < lesser.total
        group by S.S_id, S.total
        order by S.total desc;
S_id        total       RANK      
----------  ----------  ----------
2           198         1         
4           198         1         
5           183         3         
3           165         4         
1           143         5         

You don't need a CTE; you could use a subquery instead, but you'd have to repeat it.

Using a SELECT clause in a SELECT clause to produce a column (as suggested elsewhere) is AFAIK nonstandard. A self-join is standard and should be easier for the query planner to optimize (if only for that reason). Also, the above query doesn't munge the data: it doesn't add a row to the CTE only to remove it in the main query.

I prefer the sum(ca1 + ca2 + exam) construction to adding the sums. That's how the question was posed, and it asks the system to do less work (only one summation). Sure, addition is commutative, but I wouldn't depend on the query optimizer to notice.

Answer 3

Something like this maybe:

with tt(S_id,total) as (
  select S_id, sum(ca1) + sum(ca2) + sum(exam)
    from t
    group by S_id
  union
  select null, 0
  )
  select s.S_id,
         s.total,
         (select count(*)+1
            from tt as r
            where r.total > s.total) as rank
     from tt as s
     where S_id is not null;