Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
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 '13 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). –  ypercube May 9 '13 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. –  Nick Chammas May 9 '13 at 7:22
1  
Another similar question: Sorting the Table and getting the position –  ypercube May 9 '13 at 7:23
    
And here's a neat (but more inefficient) way to do it with GROUP_CONCAT: Another ranking trick –  Nathan Jolly May 9 '13 at 7:29

1 Answer 1

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

SQLFIDDLE

share|improve this answer
    
thanks lemmi put a try –  samlebo May 9 '13 at 9:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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