--method 1
 WITH T AS (
            SELECT 'A' AS CODE, 3 AS NUM FROM DUAL UNION ALL
            SELECT 'A' AS CODE, 2 AS NUM FROM DUAL UNION ALL
            SELECT 'A' AS CODE, 1 AS NUM FROM DUAL UNION ALL
            SELECT 'B' AS CODE, 4 AS NUM FROM DUAL UNION ALL
            SELECT 'B' AS CODE, 5 AS NUM FROM DUAL UNION ALL
            SELECT 'B' AS CODE, 2 AS NUM FROM DUAL UNION ALL
            SELECT 'C' AS CODE, 7 AS NUM FROM DUAL UNION ALL
            SELECT 'C' AS CODE, 8 AS NUM FROM DUAL UNION ALL
            SELECT 'C' AS CODE, 9 AS NUM FROM DUAL
          )
SELECT CODE
     , MAX( DECODE( RANK, 1, NUM ) ) AS MAXVAL1
     , MAX( DECODE( RANK, 2, NUM ) ) AS MAXVAL2
  FROM (
         SELECT T.CODE, T.NUM, RANK() OVER ( PARTITION BY T.CODE ORDER BY NUM DESC ) AS RANK
           FROM T
       )
 GROUP BY
       CODE
;
 --method 2
 select CODE
      , MAXVAL1
      , MAXVAL2
   from (  
           select rank() over (partition by code order by val desc) rk
                , max(val) over (partition by code order by val desc) as maxval1
                , VAL AS maxval2,CODE
             from (
                     select 'A' as code, 3 as val from dual
                     union all
                     select 'A' as code, 2 as val from dual
                     union all
                     select 'A' as code, 1 as val from dual
                     union all
                     select 'B' as code, 4 as val from dual
                     union all
                     select 'B' as code, 5 as val from dual
                     union all
                     select 'B' as code, 2 as val from dual
                     union all
                     select 'C' as code, 7 as val from dual
                     union all
                     select 'C' as code, 8 as val from dual
                     union all
                     select 'C' as code, 9 as val from dual
                  )  
           group by
                    code,val
        )  
  where rk = 2; 
 
 
 
--刘轶鹤