I have a mysql table
table name 'results'
Reg.No SubjectCode Attempt Grade Marks
112108 CMIS 1113 1 D 17
112110 CMIS 1114 1 A 85
112119 CMIS 1114 1 D 18
112108 CMIS 1113 2 D+ 25
112110 CMIS 1113 1 D+ 25
112107 CMIS 1113 1 B 70
112108 CMIS 1113 3 C 40
112110 CMIS 1113 2 C 40
112119 CMIS 1114 2 C 42
112120 CMIS 1114 1 D 17
This is about students' results.
Students need at least a "C" (marks>40) to pass the subject.
Until pass, students can have several attempts.
But from the second attempt maximum grade they can get is "C"
So students who was unable to pass the subject in first attempt,used to have another try until they pass the subjects
So for final results sheet the highest result will be take into account in each subjects.(highest results is given by the highest marks)
So for example highest marks of 112108 for CMIS 1113 is 40.
So likewise I want to get the students results for ecch subjects(But always maximum marks will be take into account)
Actually my expected results table should be like this
Reg.No SubjectCode Attempt Grade Marks
112110 CMIS 1114 1 A 85
112107 CMIS 1113 1 B 70
112108 CMIS 1113 3 C 40
112110 CMIS 1113 2 C 40
112119 CMIS 1114 2 C 42
112120 CMIS 1114 1 D 17
So how can I retrieve these data using sql query?
To get only one value per student, you group by Reg.No and SubjectCode and then get the max value for each subject. This query should work:
SELECT
`Reg.No`,
`SubjectCode`,
`Attempt`,
`Grade`,
MAX(`marks`) AS marks
FROM
`results`
GROUP BY
`Reg.No`, `SubjectCode`
In order to get all columns, you need two queries, which can be put together with a subquery. It will look like this:
SELECT *
FROM
(SELECT *
FROM `results`
ORDER BY `Grade` ASC) AS t
GROUP BY
`Reg.No`, `SubjectCode`
ORDER BY
`Grade` ASC
The "inner query" will get all results and order the rows by the Grade column from low to high. The "outer query" will than just group them (without the usage of the MAX function). This will result in only the last row for each grouping, which is than the max result (as we ordered it that way in the "inner query").
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments