这是那种情况,其中分析功能来为自己....
select type_cd
, id as max_id
, test_val
from (
select type_cd
, id
, test_val
, rank() over (partition by type_cd order by id desc) as rank_id
from your_table
)
where rank_id = 1
/
编辑
上面的查询还不能满足你对一个单行的需求。开槽该查询到CTE这样应该做...
with subq as
( select type_cd
, id as max_id
, test_val
from (
select type_cd
, id
, test_val
, rank() over (partition by type_cd
order by id desc) as rank_id
from your_table
)
where rank_id = 1)
select m.id as m_id
, m.test_val as m_test_val
, h.id as h_id
, h.test_val as h_test_val
from (select * from subq where type_cd = 'M') m
join (select * from subq where type_cd = 'H') h
/