3

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?

5

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).
2. 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
  • This can actually be done without a subselect. One way I know of is to use a self-join: If we join the same table ON same author AND smaller quantity, then each author will be represented in several rows, one of which has no such joined match. That row is the requested minimum quantity row for that author. (No smaller quantity exists for it, i.e. it is the smallest.) A simple WHERE then filters out all the irrelevant rows. For the query, see the example under *** LEFT JOIN (from MySQL doc) here. – Timo Apr 23 '19 at 9:57
5

Your query uses a partial group. This is allowed in older versions of MySQL (with the default setting), and have probably caused a lot of pain and mysterious bugs over the years. To see why this happens. Consider:

select id, name, min(qty) from foo group by name;
+----+------+----------+
| id | name | min(qty) |
+----+------+----------+
|  1 | John |        1 |
|  3 | Mary |        2 |
+----+------+----------+

As shown an id that is not related with the qty is in the result, why? Since you are grouping by name alone there are two id's to choose from for each name. MySQL will randomly pick an id for John and one for Mary from these groups, sometimes the result will be correct, sometimes incorrect. What you need to do is to choose the minimum qty for each name, then use that qty and the name to find the id associated with that. Both @Kondybas and @Shooter-McGavin demonstrates how this can be done in their answers.

  • 1
    This is why I was able to do this query on an older MySQL server and not in a new instance. Thank you for pointing this out. – LucasBr May 9 '18 at 19:01
4

It is amazing but your query returns the wrong result. id of the returned row isn't associated with the row containing the minimal qty. But you can get the desired result such way:

SELECT w.id, w.name 
  FROM foo AS w
  JOIN ( SELECT name, MIN(qty) AS qty
           FROM foo 
           GROUP BY name
       ) AS z ON  z.name = w.name
              AND z.qty = w.qty
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.