MySQL wrong result using MIN() function

Question

Question:

In this example, we have the grades of 5 students from school 1. We want to know which student had the lowest grade.

We were expecting to get student number 4, but SQL returns student 1

Can someone help me? Thanks in advance

Table 1:

CREATE TABLE `table1` (
  `school_id` int(11) unsigned NOT NULL,
  `student_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `grade` int(11) unsigned NOT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

Data:

INSERT INTO `table1` (`school_id`, `student_id`, `grade`)
VALUES
    (1, 1, 20),
    (1, 2, 15),
    (1, 3, 18),
    (1, 4, 12),
    (1, 5, 15);

SQL Query:

SELECT t1.`school_id`, t1.`student_id`, MIN(t1.grade)
FROM table1 as t1
WHERE t1.`school_id`=1
GROUP BY t1.`school_id`;

Printscreen:

Plain text is almost always better than a screenshot. If you can just paste that in, it usually helps. — tadman
– tadman, Commented May 16, 2016 at 21:21
You're asking for the minimum grade for the school and then basically picking a random student ID since that's not part of the group operation. This would produce a warning in MySQL 5.7. What you need to do is have an intermediate query where you ORDER BY grade and take the first from each school. — tadman
– tadman, Commented May 16, 2016 at 21:22
Agree with @tadman , grouping will group and eliminate some data, so MIN() will return whatever value in the group. — mootmoot
– mootmoot, Commented May 17, 2016 at 8:34

Strawberry · Accepted Answer · 2016-05-16 23:46:53Z

2

SELECT * FROM table1 ORDER BY grade LIMIT 1

If you want the worst performing student in each school, then that's...

SELECT x.* 
  FROM table1 x
  JOIN 
     ( SELECT school_id
            , MIN(grade) grade 
         FROM table1
        GROUP
           BY school_id
     ) y
    ON y.school_id = x.school_id
   AND y.grade = x.grade;

http://sqlfiddle.com/#!9/f44cb2/1

answered May 16, 2016 at 23:46

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rui Martins · Accepted Answer · 2016-05-16 22:51:53Z

0

With @tadman's tip, we came up with a solution:

You can find it bellow in case you came across with this same issue.

We didn't understand why we have to use the limit. if we take out the limit line, we will get a wrong result

SELECT t2.`school_id`, t2.`student_id`, t2.grade
FROM 
    (
    SELECT t1.`school_id`, t1.`student_id`, t1.grade
    FROM table1 as t1
    WHERE t1.`school_id`=1
    ORDER BY t1.`grade` ASC
    limit 4294967295
    )
as t2
GROUP BY t2.`school_id`;

edited May 16, 2016 at 22:51

answered May 16, 2016 at 21:52

Rui Martins

4,0065 gold badges38 silver badges48 bronze badges

Comments

sanastasiadis · Accepted Answer · 2016-05-17 08:36:29Z

0

Unless there are some more requirements for your problem, I guess you would be good with just:

select t1.school_id, t1.student_id, t1.grade
from table1 as t1,
(select school_id, min(grade) as grade from table1 group by school_id) as t2
where t1.school_id=t2.school_id
and t1.grade=t2.grade;

edited May 17, 2016 at 8:36

answered May 16, 2016 at 22:56

sanastasiadis

1,2001 gold badge16 silver badges24 bronze badges

Collectives™ on Stack Overflow

MySQL wrong result using MIN() function

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related