Weird behavior with MySQL GroupBy on multiple columns

I am writing a query that groups a table of users by (height, sex) and then gets the name of the user with the minimum weight for that combination of height and sex.

If you wanna skip to the actual query, here is the SQLFiddle with a minimal representative example.

If you don’t want to open that url, here is what I am talking about. Given a table such as:

| height | sex    | name    | weight |
|-------:|--------|---------|--------|
| 100    | female | Alice   | 150    |
| 100    | female | Barbara | 130    |
| 100    | female | Candice | 100    |

and a query that does this as follows:

SELECT name, min(weight) from users
group by height, sex

Why does the query output:

|  name | min(weight) |
|------:|-------------|
| Alice | 100         |

What I actually want is Candice, 100 not Alice, 100.

I figured out it chooses Alice because it is the first row, but why does it do that? It’s really weird and unexpected.

Answer

I would use a correlated subquery, but not with fancy logic:

select u.*
from users u
where u.weight = (select min(u2.weight)
                  from users u2
                  where u2.height = u.height and u2.sex = u.sex
                 );

That is, return the users whose weight is the minimum for the height/sex combination.