CODEWITHSUNDEEP
mysqlsqlsorting
InsaneCoder
InsaneCoder

Reputation: 8278

Sorting rows in MySql based on algebra on two or more columns

I am designing a Test-System for a school.The table is like this

------------------------------------------------------------------------------
    Student_id  | Total Questions  |     Questions Attempted   |    Correct     
------------------------------------------------------------------------------
     36         |       60         |           42              |       20      
     19         |       60         |           38              |       32
     119        |       60         |           37              |       31

Now, marking scheme is +3 for correct and -1 for wrong

Number of wrong questions will be calculated as wrong = attempted - correct

Questions

1) I want to give the give the student some points based on their ranks, so I want to sort the table on the decreasing order of their score i.e. score = 3 * correct - wrong.Though,I could have stored the score as well but since it is redundant data I don't want to store it into the table.How can I sort the table using SQL query.

2)When I will be updating the points of students based on their performance into the table student,I am picking student_id from result table and making updations into the student table i.e. 1 query per student.This means that if 4000 students sat for the test ,4000 queries !!! .Can I improve the situation (minimise queries)?

EDIT

Student schema for question 2

------------------------------------------------------------------------------
Student_id   |    fname    |   lname    |    contact      |     points
------------------------------------------------------------------------------

Upvotes: 0

Views: 75

Answers (3)

rrr
rrr

Reputation: 303

Question 1.

Supposing the table is named Results, and that Student_id is unique, here is a possible solution to your question:

SELECT Results.*, (3*Correct-(Total_Questions-Correct)) AS score
FROM Results
ORDER BY score DESC

Question 2.

Supposing the Students are already added to the table Students, or that they already have a score, this is a possible SQL Query to update the students table without making the 4k queries:

UPDATE StudentsTable AS s
INNER JOIN PointsTable AS p
ON s.Student_id = p.Student_id
SET
    s.Points = s.Points + (3 * p.Correct - (p.Questions_Attempted - p.Correct))

If you need to perform more tests in the future you can add a Test_ID column to you Points Table and then add a WHERE clause to the UPDATE query in order to just add up the score from a given test.

Optimization

You can optimize the queries a little bit by changing the way you calculate the score:

SELECT Results.*, (2*Correct-Total_Questions) AS score
FROM Results
ORDER BY score DESC

UPDATE StudentsTable AS s
INNER JOIN PointsTable AS p
ON s.Student_id = p.Student_id
SET
    s.Points = s.Points + (2 * p.Correct - p.Questions_Attempted)

Upvotes: 1

peterm
peterm

Reputation: 92795

To rank students by score you can do

SELECT student_id,
(
  SELECT 1 + COUNT(*)
    FROM student_results
   WHERE 3 * correct - (total - correct) >=
         3 * r.correct - (r.total - r.correct)
     AND student_id <> r.student_id
) rank
  FROM student_results r

Output:

| STUDENT_ID | RANK |
|------------|------|
|         36 |    3 |
|         19 |    1 |
|        119 |    2 |

Now you can update student points in one go using multi-table UPDATE syntax instead of hitting the database with number of update queries.

UPDATE students s JOIN
(
  SELECT student_id,
  (
    SELECT 1 + COUNT(*)
      FROM student_results
     WHERE 3 * correct - (total - correct) >=
           3 * r.correct - (r.total - r.correct)
       AND student_id <> r.student_id
  ) rank
    FROM student_results r
) q
    ON s.student_id = q.student_id
   SET s.points = s.points + 
       CASE q.rank  -- implement your logic of translating ranks to points here
          WHEN 1 THEN 100
          WHEN 2 THEN  50
          WHEN 3 THEN  10
          ELSE 0
       END;

Here is SQLFiddle demo

Upvotes: 0

Dima
Dima

Reputation: 8652

  1. you can just specify what you want in the sort

    select student_id,correct, attempted,field4,field5, (3 * correct - (attempted-correct)) as score 
from students sort by score desc

  2. yes, take a look at bulk update of sql, you can prepare the query and update 10 by 10 or 100 by 100, but not too much since sql command have limit on its length

Upvotes: 3

Related Questions