Alternative to using MIN() in a subquery?

I have the following foo table:

+----+--------+-------+
| id |  name  |  qty  |
+----+--------+-------+
| 1  |  John  |   3   |
| 2  |  John  |   1   |
| 3  |  Mary  |   5   |
| 4  |  Mary  |   2   |
| 5  |  Gary  |   3   |
| 6  |  Gary  |   4   |
| 7  |  Gary  |   5   |
+----+--------+-------+

I would like to select only id and name of minimal Qty grouped by name, with this result:

+----+--------+
| id |  name  |
+----+--------+
| 2  |  John  |
| 4  |  Mary  |
| 5  |  Gary  |
+----+--------+

The only way I could do this was by means of the following operation:

SELECT id, name FROM (SELECT id, name, MIN(qty) FROM foo GROUP BY name) AS a;

Is there a more "beautiful" (or less redundant) way to do that?

This cannot be done without a subselect as you state.

There are two steps involved.

1. For each name, find the record with the MIN(qty).

1. Return the ID for that record.

There are other approaches but the two steps remain.

It appears the query you posted has some missing info. This should be the whole thing.

SELECT id, name 
FROM (
    SELECT name, MIN(qty) minqty
    FROM foo fs
    GROUP BY name
) AS a
    JOIN foo f
        ON f.name = fs.name
        AND f.qty = fs.minqty

StackExchange Database Administrators Q#206170, answer score: 5