mysql 分组获取最小值,MySQL-选择最小值的分组依据

I have a simple table with the following data:

id | descr | val1 | val2

========================

1 | 'aa' | 1 | 5

2 | 'bb' | 2 | 7

3 | 'bb' | 3 | 1

4 | 'bb' | 4 | 19

5 | 'aa' | 5 | 1

6 | 'aa' | 6 | 8

7 | 'bb' | 7 | 11

8 | 'bb' | 8 | 3

9 | 'aa' | 9 | 16

10 | 'aa' | 10 | 9

I want to select the rows for each unique descr with the minimum value of val2.

Expected result:

id | descr | val1 | val2

========================

3 | 'bb' | 3 | 1

5 | 'aa' | 5 | 1

I tried the following query:

SELECT *, min(val2) minVal FROM test group by descr

By the result had no relation to the data in the row of the minimum value:

id | descr | val1 | val2 | minVal

==================================

1 | 'aa' | 1 | 5 | 1

2 | 'bb' | 2 | 7 | 1

Please advise.

Thank you

解决方案

One standard way of dealing with this in MySQL, which doesn't have wide support for analytic functions, is to use a subquery which identifies the minimum val2 values for each descr group. Then use this subquery to restrict the records of test to what you want appearing in your result set.

SELECT t1.*

FROM test t1

INNER JOIN

(

SELECT descr, MIN(val2) AS min_val2

FROM test

GROUP BY descr

) t2

ON t1.descr = t2.descr AND

t1.val2 = t2.min_val2

Demo here: