CODEWITHSUNDEEP
mysql
Punuth
Punuth

Reputation: 417

Filter Students by their highest marks

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?

Upvotes: 1

Views: 1797

Answers (3)

2ndkauboy
2ndkauboy

Reputation: 9377

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").

Upvotes: 3

Bohemian
Bohemian

Reputation: 425013

Use a subquery to establish which is the highest mark and join to that:

SELECT r.`Reg.No`, r.SubjectCode, MAX(Attempt), r.Grade, r.marks
FROM results r
JOIN (SELECT `Reg.No`, SubjectCode, MAX(marks) AS marks
   FROM results
   GROUP BY 1, 2) m
   ON m.`Reg.No` = r.`Reg.No`
   AND m.SubjectCode = r.SubjectCode
   AND m.marks = r.marks
GROUP BY 1, 2, 4, 5

The outer grouping is to break ties when a student achieves the same mark for a subject multiple times.

Upvotes: 1

Shehroz Asmat
Shehroz Asmat

Reputation: 161

try like this by 

SELECT * FROM results WHERE marks > 40 Order By Marks Desc

this will list the result greater than 40 and highest marks will be shown first you can use limit too so you can fetch records according to your need

Upvotes: 0

Related Questions