StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGet Rank In pivot Query and Get Id rank in query
text
Body
copied!<p>I have a mysql table like:</p> <p>Sample Data</p> <pre> ID STUDENT_ID Branch_id Class_id Exam_id Subject_id Numbers Date 1 653 5 1 1 8 60 2012-01-01 2 653 5 1 1 9 40 2012-01-01 3 653 5 1 1 10 80 2012-01-01 4 653 5 1 1 11 50 2012-01-01 5 653 5 1 1 12 65 2012-01-01 6 653 5 1 1 13 33 2012-01-01 7 653 5 1 1 15 86 2012-01-01 8 222 5 1 1 8 100 2012-01-01 9 222 5 1 1 9 80 2012-01-01 10 222 5 1 1 10 92 2012-01-01 11 222 5 1 1 11 50 2012-01-01 12 222 5 1 1 12 65 2012-01-01 13 222 5 1 1 13 33 2012-01-01 7 222 5 1 1 15 86 2012-01-01 </pre> <p>I need to get rank of student in class even if there two student are equal in marks. example if two students get 600 marks out of 600 marks that means both are on first position. </p> <p>I search many site and I found <a href="http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query" rel="nofollow">this post </a> that works what I need but in this post the correct column already sum of marks but in my table I need to sum(numbers) then get rank of of student depend on there total marks.</p> <p>This all when I fetched the result of single student. </p> <p>At same data when I fetched result for all class there is two thing needs. First row to column pivot query that display result in excel format so I use row to colum query like:</p> <pre><code>SELECT m.* FROM ( select student_id, branch_id, class_id, numbers, exam_id, date , sum(numbers*(1-abs(sign(subject_id-1)))) as subs1 , sum(numbers*(1-abs(sign(subject_id-2)))) as subs2 , sum(numbers*(1-abs(sign(subject_id-3)))) as subs3 , sum(numbers*(1-abs(sign(subject_id-4)))) as subs4 , sum(numbers*(1-abs(sign(subject_id-5)))) as subs5 , sum(numbers*(1-abs(sign(subject_id-6)))) as subs6 from results GROUP BY student_id, branch_id, class_id, exam_id ) m where branch_id = '6' and class_id = '1' and exam_id = '1' and YEAR(date) = '2013' </code></pre> <p>Required Result In class query like:</p> <pre> ID Name Math English Science Total Percent Position Rank </pre> <p>And I got result fine by this query. but still there is problem in rank how to get rank of students in same query?</p> <p>Can any one help?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload