0

I need an sql that will rank students order by marks scored in a specific examtyp e.g CAT1 and give exact position even if the students have equal marks e.g in this sample data admNo 2525 and admNo 4093 scored equal marks in CAT1, so i need an sql that will fetch the positions as the same and skip give the next student number 3. the following sql that i tried does not consider a scenario like this.

$sql = "SELECT q1.rownum
FROM 
    (
        SELECT *, @rownum:=@rownum + 1 AS rownum
        FROM $table t, (SELECT @rownum:=0) r 
        WHERE examtyp =? AND termId=? AND year=? 
        ORDER BY points DESC
    ) q1
WHERE q1.admNo=$admNo"; 

resid  admNo streamIdexamtyptermIdmarks gradepointyear
1       2129    0   CAT1    1   525     C   62  2013
2       4093    0   CAT1    1   569     B+  69  2013
3       2129    0   CAT2    1   550     B+  67  2013
4       4093    0   CAT2    1   556     B+  68  2013
6       2129    0   FINAL   1   559     B+  68  2013
7       2129    0   AVERAGE 1   545     B   66  2013
7       4093    0   FINAL   1   581     B+  70  2013
8       4093    0   AVERAGE 1   569     B+  69  2013
9       2525    9   CAT1    1   569     B+  69  2013
6
  • 2
    Where does aggregate_points come from? That SQL does not seem to match the outpit. Please add source table and data and desired output so we don't have to guess
    – gbn
    May 9, 2013 at 7:12
  • Yes, as @gbn points out, the column names in the table do not match the names in the query (examCategory - examtyp, aggregate_points - points). May 9, 2013 at 7:22
  • 1
    I've answered a related MySQL question about ranking rows by some kind of score. My answer uses techniques described here on SO. If you provide sample data and expected output, we can better help you. May 9, 2013 at 7:22
  • 1
    Another similar question: Sorting the Table and getting the position May 9, 2013 at 7:23
  • And here's a neat (but more inefficient) way to do it with GROUP_CONCAT: Another ranking trick May 9, 2013 at 7:29

1 Answer 1

3
SELECT t1.id, mark,
(SELECT COUNT(*) FROM marktable t2 WHERE t2.mark > t1.mark) +1
AS rnk
FROM marktable t1 order by rnk;

SQLFIDDLE

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.