Selecting max value for a column in Oracle

Question

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_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.

Answer 1

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 

/

Answer 2

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.

Answer 3

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 t1 m, t1 h
WHERE m.type_cd = 'M'
  AND h.type_cd = 'H'
QUALIFY RANK() OVER (ORDER BY m.id DESC) = 1
  AND RANK() OVER (ORDER BY h.id DESC) = 1;