Reputation:
In my table, in one hour, 50 students will submit their marks in some exam everyday.
Following is the table
ID StudID Marks Rank
1 101 56
2 102 23
3 103 84
4 104 96
5 105 44
I want when all these records are inserted, the rank should get auto calculated and get insterted against each record. For example, Rank column will get filled like this:
ID StudID Marks Rank
1 101 56 3
2 102 23 5
3 103 84 2
4 104 96 1
5 105 44 4
I want to create a single query for this in mysql. Would it be done in a single query or will it require any functions or procedure? How should I write query, function or procedure for above logic in mysql?
Note: I have to implement above logic using PHP.
Upvotes: 0
Views: 118
Reputation: 41
I assuming the student rerun the insert query, so when the student submit their mark they run query insert into ... Try this:
INSERT INTO <thetable> (ID, StudID, Marks, Rank)
VALUES (<theid>, <thestudentid>, <themark>, NULL)
ON DUPLICATE KEY UPDATE Rank = (SELECT COUNT(*)+1
FROM <thetable>
WHERE Rank IS NOT NULL);
Another assumption: initial value for column "Rank" is NULL, or if empty string change the condition Rank IS NOT NULL
to Rank = ''
Upvotes: 0
Reputation: 263713
UPDATE TableName a
INNER JOIN
(
SELECT StudID, Marks,
(SELECT COUNT(DISTINCT b.Marks)
FROM tableName b
WHERE a.Marks <= b.Marks
) as rank
FROM tableName a
ORDER BY Marks DESC
) b ON a.StudID = b.StudID
SET a.Rank = b.rank
Upvotes: 1