Oracle SQL-将结果限制为最大值

我正在使用查询：

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

我得到多行，因为SGB_TYP_CODE具有不同的值。我只想要最高任期的结果。我试过使用‘Keep Density_RANK’，但我无法使用它。

谢谢。

---

解决方案

下面是如何使用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

完整示例：

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