表:T
name age dept
aa 24 1
bb 28 3
cc 30 2
dd 34 1
ee 35 1
ff 45 2
hh 37 3
欲求的检索信息结果
dept name age
1 ee 35
2 ff 45
3 hh 37
以下语句要做如何修改满足该要求?谢谢
select dept,name,max(age)
from T
group by dept,name;
------解决方案--------------------
用自关联子查询就行了
select * from t a where
age = (select max(age) from t b a.dept = b.dept )
------解决方案--------------------
- SQL code
select dept, name, age from( select t.*, rank() over(partition by dept order by age desc) rk from t) where rk=1;