Mysql Sql statement to Get Position of Student Ranked in order of marks scored

Question

I need an sql that will give the position of the student ranked in order of marks scored in a specific examtyp e.g. CAT! only.the sql below gives the position of the student but does not distinguish the examtyp.it ranks without considering the examtyp.

res_id admNo stream examtyp termId  marks   grade   points  year
   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




$sql = "SELECT 1 + (SELECT count(*) FROM $table a 
        WHERE a.total_marks > b.total_marks ) AS rank 
        FROM $table b WHERE admNo=? AND examCategory=? AND termId=? AND year=?  
        ORDER BY rank LIMIT 1";
$res = $this->db->query($sql, array($admNo, $examCategory, $term, $year));

Welcome to StackOverflow! Provide your table definitions, sample data, and desired output and **what you've got yourself so far**, so that we can see your effort and won't guess what is it really you want us to help you with. — peterm, May 08 '13 at 18:04
possible duplicate of [Generate serial number in mysql query](http://stackoverflow.com/questions/11094466/generate-serial-number-in-mysql-query) — GarethD, May 08 '13 at 18:13
See http://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table — PM 77-1, May 08 '13 at 18:14
Make a [fiddle](http://sqlfiddle.com) so you can play with it yourself. — hd1, May 09 '13 at 05:52

score 0 · Answer 1 · answered May 08 '13 at 18:13

0

Try the Query

SET @rank=0;
select
@rank := @rank+1 AS rank 
result_id,
marks_scored,
admNo,
Aggregate_points,
year
from tale_name
order by marks_scored DESC

answered May 08 '13 at 18:13

Prahalad Gaggar

11,389
16
53
71

function rank_senior($results_table, $array) { $this->db->select()->from($results_table)->where($array)->order_by('aggregate_points', 'DESC'); $query = $this->db->get(); if ($query->num_rows() > 0) { return $query->result(); } else { return FALSE; } }this is the query that i use but i want a sql that will give me the position of the student at any time i need it without having to display the ranked results on the browser – samlebo May 08 '13 at 18:52
@user2224459 please **EDIT** your Question. – Prahalad Gaggar May 08 '13 at 19:00
I have edited the question, the table has sample data and i want the sql provided ranks students but does not distinguish the examtyp e.g. CAT1.i need sql that ranks based on marks scored in CAT1 only or other examtypes.thanks guys – samlebo May 09 '13 at 05:45

Meherzad · Answer 2 · 2013-05-09T06:09:52.797

Try this query

Query 1:

select 
@rn:=if(@prv=examtyp, @rn+1, 1) as rId, 
admNo,
@prv:=examtyp as exmtyp,
marks
from table1
join
(select @rn:=0,@prv:='') tmp
order by exmtyp, marks desc

SQL FIDDLE:

| RID | ADMNO |  EXMTYP | MARKS |
---------------------------------
|   1 |  4093 | AVERAGE |   569 |
|   2 |  2129 | AVERAGE |   545 |
|   1 |  4093 |    CAT1 |   569 |
|   2 |  2129 |    CAT1 |   525 |
|   1 |  4093 |    CAT2 |   556 |
|   2 |  2129 |    CAT2 |   550 |
|   1 |  4093 |   FINAL |   581 |
|   2 |  2129 |   FINAL |   559 |

EDIT

Query 1:

select * from (
select 
@rn:= @rn+1 as rId, 
admNo,
examtyp,
marks
from table1
join
(select @rn:=0) tmp
where examtyp='CAT1'
order by examtyp, marks desc
) tmp where tmp.admNo=2129

SQL FIDDLE:

| RID | ADMNO | EXAMTYP | MARKS |
---------------------------------
|   2 |  2129 |    CAT1 |   525 |

thanks sir,but i need an sql that ranks students in a specific examtyp like CAT1 only and not all the examtyp. e.g i need position of admNo 4093 in CAT1 examtyp ranked in order of marks — samlebo, May 09 '13 at 05:53
You can add filter accordingly for your exam type in where clause... Check the edit — Meherzad, May 09 '13 at 06:06

Kshitij · Answer 3 · 2013-05-09T08:54:37.187

try this -

SELECT q1.rownum
FROM 
    (
        SELECT *, @rownum:=@rownum + 1 AS rownum
        FROM $table t, (SELECT @rownum:=0) r 
        WHERE examtyp = 'CAT1'
        ORDER BY marks
    ) q1
WHERE q1.admNo=?

2) Since you modified the requirement to get equal ranks for same marks, u might need to do something like this -

SELECT q1.rownum
FROM 
    (
        SELECT *, @rownum:=@rownum + 1 AS rownum
        FROM 
            (SELECT DISTINCT marks FROM table1 t WHERE t.examtyp = 'CAT1' ORDER BY t.marks) q2, 
            (SELECT @rownum:=0) r 
    ) q1,
    table1 t2
WHERE 
    t2.examtyp = 'CAT1'
    AND t2.marks=q1.marks
    AND t2.admNo=?;

Above, you need to change examCategory at two places.

This is not the most optimized query..but it will do ur work.

3) as per your third requirement to get incremented count of next student, this might do the trick -

SELECT ROWNUM
FROM
(
    SELECT q1.marks, min(q1.rownum) AS rownum
    FROM 
    (
        SELECT t1.marks, @rownum:=@rownum + 1 AS rownum
        FROM 
            table1 t1, 
            (SELECT @rownum:=0) r
        WHERE
            t1.examtyp='CAT1'
        ORDER BY t1.marks asc
    ) q1 
    GROUP BY q1.marks
) q2,
table1 t2
WHERE 
    t2.examtyp = 'CAT1'
    AND t2.marks=q2.marks;
    AND t2.admNo=?;

thanks this is working fine. i now need a sql that will ranks the students in a certain examtyp e,g. CAT1 and give the position even where there are ties in marks it can give the position of the student — samlebo, May 09 '13 at 06:25
this sql does not work well when the students have equal marks.i need an sql that can give students the same potition when they have equal marks.thanks — samlebo, May 09 '13 at 06:50
thanks, this query gives students with equal marks the same position but i want it to count the number of students who have shared the position so that it can give the next student the correct position in class considering the number of students above him/her in points scored.hope my grammer is understood.thanks again — samlebo, May 09 '13 at 08:25

score 0 · Accepted Answer · answered May 09 '13 at 08:11

This should work for you:

SELECT  res_ID,
        admNo,
        stream,
        examtyp,
        termId,
        grade,
        points,
        `year`,
        Position
FROM    (   SELECT  @r:= CASE WHEN @e = examtyp THEN @r + CASE WHEN @p = points THEN 0 ELSE @i END ELSE 1 END Position,
                    @i:= CASE WHEN @p = points THEN @i + 1 ELSE 1 END incr,
                    @e:= Examtyp,
                    @p:= points,
                    res_ID,
                    admNo,
                    stream,
                    examtyp,
                    termId,
                    grade,
                    points,
                    `year`
            FROM    T,
                    (SELECT @e:= '') e,
                    (SELECT @r:= 0) r,
                    (SELECT @p:= 0) p,
                    (SELECT @i:= 0) i
            ORDER BY examtyp, points
        ) T
WHERE   T.admNo = 4093
AND     T.Examtyp = 'CAT1'

It uses the same principle of using variables that has been suggested, however also partitions by examtyp, resetting the position to 0 for each new exam type, it also records the previous points to deal with ties, so if 3 people get the same mark they all get the same position.

Example on SQL Fiddle

Note in the bottom pane of the fiddle the results for AVERAGE are equal so both get position = 1

Mysql Sql statement to Get Position of Student Ranked in order of marks scored

4 Answers4

EDIT

SQL FIDDLE: