mysql> create table t1(id int,level int,gap int);
mysql> insert into t1 values (1,6,50),(1,5,10),(2,5,10),(2,5,12),(3,8,10),
(3,8,2),(3,9,6),(3,9,4);
This two commands can create a table like following
mysql> select * from t1;
+------+-------+------+
| id | level | gap |
+------+-------+------+
| 1 | 6 | 50 |
| 1 | 5 | 10 |
| 2 | 5 | 10 |
| 2 | 5 | 12 |
| 3 | 8 | 10 |
| 3 | 8 | 2 |
| 3 | 9 | 6 |
| 3 | 9 | 4 |
+------+-------+------+
8 rows in set (0.00 sec)
I want to select one record when the level is maximal but the gap is minimal in every group by id.So I think this code should work for me
mysql> select id,max(level),first(gap) from (select * from t1 group by id,
level,gap) as tem group by id;
But I find the mysql have no first function.This is current solution.
select
tt.id,tt.level,min(tt.gap) min_gap
from (
select * from t1 tt1
where not exists(
select 1 from t1 tt2 where tt1.id=tt2.id and tt1.level<tt2.level
)
) tt
group by tt.id,tt.level;
It will give a right answer like
But is there any concise method can solve this question?
