Selecting max value for a column in Oracle
I have a table which has data like this
id test_val type_cd
#-------------------------
101 TEST22 M
102 TEST23 M
103 TEST01 M
103 TEST01 H
104 TEST02 M
104 TEST02 H
105 TEST03 H
I would like to fetch the max(id) for each type_cd and its corresponding test_val in a single row output as below.
The expected output is:
M_id M_Test开发者_Python百科_Val H_id H_Test_Val
#-----------------------------------
104 TEST02 105 TEST03
If I have to fetch only the max(id) for each type_cd I'll have my query like this
select max(case when type_cd='M' then max(id) else null end) as M_id,
max(case when type_cd='H' then max(id) else null end) as H_id
from t1
group by type_cd;
I'm not sure how to get the test_val for the max(id)
for each type_cd
.
This is the sort of scenario where analytic functions come into their own....
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
/
edit
The above query doesn't satisfy your need for a single row. Slotting that query into a CTE like this ought to do it...
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
/
I wrote the code as below and it works as expected.
select max(case when type_cd='M' then id else null end) as m_id,
max(case when type_cd='M' then test_val else null end) as m_test_val,
max(case when type_cd='H' then id else null end) as h_id,
max(case when type_cd='M' then test_val else null end) as h_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;
Not sure if this is the optimal way. But works just as I want it to.
精彩评论