CODEWITHSUNDEEP
mysqlsqlselect
codernac
codernac

Reputation: 103

MySQL return min value but not null

I have a table where there are columns students and grade obtained(A-F). A student can appear for test more than once. Sometimes students register but do not appear for test so the grade is not entered but student record entry is made.

I want to get best grade of each student. When I do min(grade) if there is any record with null, null gets selected instead of 'A-F' which indicate proper results. I want to get min of grade if grade exists or null if there are no grades.

SELECT `name`,min(grade) FROM `scores` group by `name`

Id | Name | Grade
1  | 1    | B
2  | 1    | 
3  | 1    | A
4  | 2    | C
5  | 2    | D

For name 1 it is fetching second record not the third one having 'A'.

Upvotes: 1

Views: 10704

Answers (2)

mistwalker
mistwalker

Reputation: 781

Change your query slightly to -

SELECT `name`,min(grade) FROM `scores` WHERE grade <> "" group by `name`

If the name has a grade/s assigned to it then the lowest will be returned else the resultset will be null

Upvotes: 0

Mureinik
Mureinik

Reputation: 311163

As per the conversations in the comments, the easiest solution may be to convert your empty strings to null, and let the builtin min function do the heavy lifting:

ALTER TABLE scores MODIFY grade VARCHAR(1) NULL;

UPDATE scores
SET    grade = null
WHERE  grade = '';

SELECT   name, MIN(grade)
FROM     scores
GROUP BY name

If this is not possible, a dirty trick you could use is to have a case expression convert the empty string to a something you know will come after F:

SELECT   name, 
         MIN(CASE grade WHEN '' THEN 'DID NOT PARTICIPATE' ELSE grade END)
FROM     scores
GROUP BY name

And if you really need the empty string back, you can have another case expression around the min:

SELECT name, CASE best_grade WHEN 'HHH' THEN '' ELSE best_grade END
FROM   (SELECT   name, 
                 MIN(CASE grade WHEN '' THEN 'HHH' ELSE grade END) AS 
                   best_grade
        FROM     scores
        GROUP BY name) t

Upvotes: 2

Related Questions