使用MySQL聚合函数MIN时如何获取正确的行数据？

Great question! Let’s break this down clearly, starting with the behavior you already noticed and then tackling the MIN/MAX scenario you’re curious about.

First: AVG, SUM, and Unaggregated Fields #

You’re absolutely right about AVG, SUM, and similar aggregate functions. When you run a query like:

SELECT AVG(amount), name, `desc` FROM some_table;

The name and desc values in the result are undefined and non-deterministic according to standard SQL. Here’s why:

• Aggregate functions like AVG calculate a single value across the entire dataset (or a group, if you use GROUP BY). This result isn’t tied to any specific row in the table.
• When you include non-aggregated fields without a GROUP BY clause, the database will pull those values from some row in the dataset—but there’s no rule dictating which row. Different database systems, or even the same system with different execution plans, might return different values for those fields.

Now: MIN/MAX Aggregate Functions #

You mentioned MIN as a "different type" of aggregate function, and it’s easy to see why it might feel that way. At first glance, you might think SELECT MIN(amount), name FROM some_table; would return the name associated with the smallest amount—but this is not guaranteed by standard SQL.

Even though MIN finds the smallest value in the column, the non-aggregated name field still isn’t linked to that specific row. Some databases (like MySQL in certain modes) might have non-standard behavior that seems to pair them, but this is an implementation detail, not a reliable standard.

If you actually want to get the row(s) that correspond to the MIN (or MAX) value, you need to explicitly tie them together. Common approaches include:

• Using a subquery to filter for the min value:

  SELECT amount, name, `desc` FROM some_table WHERE amount = (SELECT MIN(amount) FROM some_table);
  

• Using window functions to rank rows and pick the top one:

  SELECT amount, name, `desc`
  FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY amount ASC) AS row_rank
    FROM some_table
  ) ranked_table
  WHERE row_rank = 1;
  

The key takeaway here is: any non-aggregated field in a SELECT statement without a GROUP BY clause has an undefined value, regardless of whether you’re using AVG/SUM or MIN/MAX.

内容的提问来源于stack exchange，提问作者Brett