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.

Please show sample data and expected output – OldProgrammer Jul 27 '17 at 17:40 — OldProgrammer, Jul 27 '17 at 17:40
Fixed yesterday. Thanks for your input. – Lyndey Jul 28 '17 at 20:12 — Lyndey, Jul 28 '17 at 20:12

Matthew McPeak · Accepted Answer · 2017-07-27T17:59:25.773

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

Solved! Thank you @matthewmcpeak – Lyndey Jul 28 '17 at 20:09 — Lyndey, Jul 28 '17 at 20:09

Oracle SQL - limit results to max value

1 Answers1