Oracle SQL - limit results to max value

Question

I'm using the query:

select
  SGB_ID,
  max(SGB_TERM_CODE_EFF)max_term,
  SGB_TYP_CODE
from SGB
group by
  SGB_ID,
  SGB_TYP_CODE
order by 1

I'm getting multiple rows, as the SGB_TYP_CODE has different values. I just want the result from the maximum term. I've tried using 'keep dense_rank', but I can't get it to work.

Thanks.

Answer 1

Here is how to do that with MAX()...KEEP():

SELECT sgb_id,
       MAX (sgb_term_code_eff) max_term,
       MAX (sgb_typ_code) 
            KEEP ( DENSE_RANK FIRST 
                   ORDER BY sgb_term_code_eff DESC ) sgb_typ_code
FROM   sgb
GROUP BY sgb_id
ORDER BY 1

Full example:

with sgb ( sgb_id, sgb_term_code_eff, sgb_typ_code ) AS 
 ( SELECT 1, 'A', 'ACODE' FROM DUAL UNION ALL
   SELECT 1, 'B', 'BCODE' FROM DUAL UNION ALL
   SELECT 1, 'Z', 'ZCODE' FROM DUAL UNION ALL
   SELECT 1, 'D', 'DCODE' FROM DUAL UNION ALL
   SELECT 2, 'A', 'ACODE' FROM DUAL UNION ALL
   SELECT 2, 'Q', 'QCODE' FROM DUAL UNION ALL
   SELECT 2, 'Q', 'QCODE' FROM DUAL UNION ALL
   SELECT 3, 'A', 'ACODE' FROM DUAL )
SELECT sgb_id,
       MAX (sgb_term_code_eff) max_term,
       MAX (sgb_typ_code) KEEP ( DENSE_RANK FIRST ORDER BY sgb_term_code_eff DESC ) sgb_typ_code
FROM   sgb
GROUP BY sgb_id
ORDER BY 1

SGB_ID                                 MAX_TERM SGB_TYP_CODE 
-------------------------------------- -------- ------------ 
                                     1 Z        ZCODE
                                     2 Q        QCODE
                                     3 A        ACODE