Rank the students based on pass marks

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  October 18, 2018 at 12:25 pm

  #381393

  I would like to find out the rank of the student based on passed marks. In this case passed mark is 35 marks.

  
create table class_score
(
name varchar(200),
subject varchar(200),
mark int
);

  insert into class_score values('saravanan','botany',50);
insert into class_score values('saravanan','zoology',50);
insert into class_score values('vadivel','botany',100);
insert into class_score values('vadivel','zoology',50);
insert into class_score values('raj','botany',30);
insert into class_score values('raj','zoology',80);

  Desired output:

  name	total_marks	rnk
  vadivel	150	1
  saravanan	100	2
  raj	110	3

  
  As we can see total marks of raj is higher than saravanan's . But saravanan's rank is lower than Raj's because Raj is scored less than 35 mark in one subject.Those who scored less than 35 marks is considered as failed and their rank should be higher than rank of who passed all the subject.
  
What I tried so for actually works only to certain limit
select distinct name,total_marks,
dense_rank() over (order by
(case when sc.name not in (select name from score where mark<35)
then (total_marks) else total_marks*-1 end) desc) rnk
from score sc
order by rnk


create table class_score
(
name varchar(200),
subject varchar(200),
mark int
);

  insert into class_score values('saravanan','botany',50);
insert into class_score values('saravanan','zoology',50);
insert into class_score values('vadivel','botany',100);
insert into class_score values('vadivel','zoology',50);
insert into class_score values('raj','botany',30);
insert into class_score values('raj','zoology',80);
insert into class_score values('mani','botany',25);
insert into class_score values('mani','zoology',100);
  with score as
(
select name,mark,subject,
sum(mark) over(partition by name) as total_marks
 from class_score
 ) select distinct name,total_marks,
dense_rank() over (order by
(case when sc.name not in (select name from score where mark<35)
then (total_marks) else total_marks*-1 end) desc) rnk
from score sc
order by rnk
desired output:

  name	total_marks	rnk
  vadivel	150	1
  saravanan	100	2
  mani	125	3
  raj	110	4

  In this case I am not getting desired result as both Mani and Raj are failed in one subject but Mani scored more marks than Raj and his rank should be better than Raj.

  Saravanan

• DesNorton

  SSC-Insane

  Points: 24278

  More actions

  October 18, 2018 at 1:13 pm

  #2010299

  You need to check for the number of failed subjects first
  DECLARE @PassMark int = 35;

  SELECT
  cs.name
, TotalMarks = SUM(cs.mark)
, NumFailures = SUM(CASE WHEN cs.mark < @PassMark THEN 1 ELSE 0 END)
, rnk = DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN cs.mark < @PassMark THEN 1 ELSE 0 END), SUM(cs.mark) DESC)
FROM class_score as cs
GROUP BY cs.name;

  ---

  How to post data/code on a forum to get the best help.

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  October 18, 2018 at 8:52 pm

  #2010354

  DesNorton - Thursday, October 18, 2018 1:12 PM

  You need to check for the number of failed subjects first
  DECLARE @PassMark int = 35;

  SELECT
  cs.name
, TotalMarks = SUM(cs.mark)
, NumFailures = SUM(CASE WHEN cs.mark < @PassMark THEN 1 ELSE 0 END)
, rnk = DENSE_RANK() OVER (ORDER BY SUM(CASE WHEN cs.mark < @PassMark THEN 1 ELSE 0 END), SUM(cs.mark) DESC)
FROM class_score as cs
GROUP BY cs.name;

  Excellent solution. Will wait for sometime  if someone gives better solution or else mark it as answer. Thanks Desnorton.

  Saravanan